# -*- codeing = utf-8 -*-
# @Time: 2021/5/27 23:32
# @Author: Foxhuty
# @File: scores_process.py
# @Software: PyCharm
# @Based on python 3.9
import pandas as pd
import numpy as np
import os
import random
import time
import warnings
import re
import datetime
from sympy import symbols, solve

warnings.simplefilter('ignore', FutureWarning)


class ScoreAnalysis(object):
    """
    用高中成绩分析，计算平均分，有效分，有效分人数，错位生人数，成绩对比，学科评定，考室安排，学生个人成绩单等。
    简单高效地完成考试成绩分析。
    """
    arts_scores = []
    science_scores = []

    def __init__(self, path):
        self.path = path
        self.df_arts = pd.read_excel(self.path, sheet_name='文科',
                                     dtype={
                                         '班级': str,
                                         '序号': str,
                                         '名次': str,
                                         '考生号': str,
                                         '考号': str

                                     }
                                     )
        self.df_science = pd.read_excel(self.path, sheet_name='理科',
                                        dtype={
                                            '班级': 'str',
                                            '序号': 'str',
                                            '名次': 'str',
                                            '考生号': 'str',
                                            '考号': 'str'

                                        }
                                        )

    def __str__(self):
        return f'对{os.path.basename(self.path)}进行成绩数据处理，生成excel表.'

    def get_av(self):
        """
        计算各科平均分
        :return: 文科，理科各班各科平均分
        """
        av_class_arts = self.df_arts.groupby(['班级'])[
            ['语文', '数学', '英语', '政治', '历史', '地理', '总分']].mean().round(2)
        av_general_arts = self.df_arts[['语文', '数学', '英语', '政治', '历史', '地理', '总分']].apply(np.nanmean,
                                                                                                       axis=0).round(2)
        av_general_arts.name = '年级'
        av_arts = av_class_arts.append(av_general_arts)
        student_number = self.get_student_number_class(self.df_arts)
        av_arts = av_arts.join(student_number)
        order = ['参考人数', '语文', '数学', '英语', '政治', '历史', '地理', '总分']
        av_arts = av_arts[order]

        av_class_science = self.df_science.groupby(['班级'])[
            ['语文', '数学', '英语', '物理', '化学', '生物', '总分']].mean().round(2)
        av_general_science = self.df_science[['语文', '数学', '英语', '物理', '化学', '生物', '总分']].apply(np.nanmean,
                                                                                                             axis=0).round(
            2)
        av_general_science.name = '年级'
        av_science = av_class_science.append(av_general_science)
        student_number_science = self.get_student_number_class(self.df_science)
        av_science = av_science.join(student_number_science)
        order_science = ['参考人数', '语文', '数学', '英语', '物理', '化学', '生物', '总分']
        av_science = av_science[order_science]
        return av_arts, av_science

    @staticmethod
    def get_student_number_class(df_data):
        student_number_class = df_data['班级'].value_counts()
        student_number_class.name = '参考人数'
        student_number_class['年级'] = student_number_class.sum()
        return student_number_class

    def get_goodscores_arts(self, goodtotal_arts):
        """
        计算文科各科有效分
        goodtotal:划线总分，高线，中线，低线
        """
        chn = self.get_subject_good_score(self.df_arts, '语文', goodtotal_arts)
        math = self.get_subject_good_score(self.df_arts, '数学', goodtotal_arts)
        eng = self.get_subject_good_score(self.df_arts, '英语', goodtotal_arts)
        pol = self.get_subject_good_score(self.df_arts, '政治', goodtotal_arts)
        his = self.get_subject_good_score(self.df_arts, '历史', goodtotal_arts)
        geo = self.get_subject_good_score(self.df_arts, '地理', goodtotal_arts)
        if (chn + math + eng + pol + his + geo) > goodtotal_arts:
            math = math - 1
        if (chn + math + eng + pol + his + geo) < goodtotal_arts:
            eng = eng + 1

        return chn, math, eng, pol, his, geo, goodtotal_arts

    def get_goodscores_science(self, goodtotal_science):
        """
        计算理科各科有效分
        goodtotal:划线总分，高线，中线，低线
        """
        chn = self.get_subject_good_score(self.df_science, '语文', goodtotal_science)
        math = self.get_subject_good_score(self.df_science, '数学', goodtotal_science)
        eng = self.get_subject_good_score(self.df_science, '英语', goodtotal_science)
        phys = self.get_subject_good_score(self.df_science, '物理', goodtotal_science)
        chem = self.get_subject_good_score(self.df_science, '化学', goodtotal_science)
        bio = self.get_subject_good_score(self.df_science, '生物', goodtotal_science)

        if (chn + math + eng + phys + chem + bio) > goodtotal_science:
            math = math - 1
        if (chn + math + eng + phys + chem + bio) < goodtotal_science:
            eng = eng + 1

        return chn, math, eng, phys, chem, bio, goodtotal_science

    def single_double_arts(self, chn, math, eng, pol, his, geo, total):
        """
        计算各科各班单有效和双有效人数
        :param chn:
        :param math:
        :param eng:
        :param pol:
        :param his:
        :param geo:
        :param total:
        :return: result_single,result_double
        """
        single_chn_arts, double_chn_arts = self.get_single_double_score(self.df_arts, '语文', chn, total)
        single_math_arts, double_math_arts = self.get_single_double_score(self.df_arts, '数学', math, total)
        single_eng_arts, double_eng_arts = self.get_single_double_score(self.df_arts, '英语', eng, total)
        single_pol_arts, double_pol_arts = self.get_single_double_score(self.df_arts, '政治', pol, total)
        single_his_arts, double_his_arts = self.get_single_double_score(self.df_arts, '历史', his, total)
        single_geo_arts, double_geo_arts = self.get_single_double_score(self.df_arts, '地理', geo, total)
        single_total_arts, double_total_arts = self.get_single_double_score(self.df_arts, '总分', total, total)
        name_num = self.df_arts.groupby(['班级'])['姓名'].count()
        name_num.name = '参考人数'
        arts_single = pd.concat([name_num, single_chn_arts, single_math_arts, single_eng_arts,
                                 single_pol_arts, single_his_arts, single_geo_arts, single_total_arts],
                                axis=1)
        arts_double = pd.concat(
            [name_num, double_chn_arts, double_math_arts, double_eng_arts,
             double_pol_arts, double_his_arts, double_geo_arts, double_total_arts], axis=1)

        arts_single.loc['文科共计'] = [arts_single['参考人数'].sum(),
                                       arts_single['语文'].sum(),
                                       arts_single['数学'].sum(),
                                       arts_single['英语'].sum(),
                                       arts_single['政治'].sum(),
                                       arts_single['历史'].sum(),
                                       arts_single['地理'].sum(),
                                       arts_single['总分'].sum()
                                       ]
        # 新增一列上线率
        arts_single['上线率'] = arts_single['总分'] / arts_single['参考人数']
        arts_single['上线率'] = arts_single['上线率'].apply(lambda x: format(x, '.2%'))
        arts_double.loc['文科共计'] = [arts_double['参考人数'].sum(),
                                       arts_double['语文'].sum(),
                                       arts_double['数学'].sum(),
                                       arts_double['英语'].sum(),
                                       arts_double['政治'].sum(),
                                       arts_double['历史'].sum(),
                                       arts_double['地理'].sum(),
                                       arts_double['总分'].sum()]
        good_scores_dict = {'参考人数': ' ', '语文': chn, '数学': math, '英语': eng,
                            '政治': pol, '历史': his, '地理': geo, '总分': total}
        good_scores = pd.DataFrame(good_scores_dict, index=['班级'])
        return arts_single, arts_double, good_scores

    def single_double_science(self, chn, math, eng, phys, chem, bio, total):
        """
               计算理科各科各班上单有效和双有效分人数
               """

        single_chn_science, double_chn_science = self.get_single_double_score(self.df_science, '语文', chn, total)
        single_math_science, double_math_science = self.get_single_double_score(self.df_science, '数学', math, total)
        single_eng_science, double_eng_science = self.get_single_double_score(self.df_science, '英语', eng, total)
        single_phys_science, double_phys_science = self.get_single_double_score(self.df_science, '物理', phys, total)
        single_chem_science, double_chem_science = self.get_single_double_score(self.df_science, '化学', chem, total)
        single_bio_science, double_bio_science = self.get_single_double_score(self.df_science, '生物', bio, total)
        single_total_science, double_total_science = self.get_single_double_score(self.df_science, '总分', total, total)

        name_num = self.df_science.groupby(['班级'])['姓名'].count()
        name_num.name = '参考人数'

        science_single = pd.concat([name_num, single_chn_science, single_math_science, single_eng_science,
                                    single_phys_science, single_chem_science, single_bio_science, single_total_science],
                                   axis=1)
        science_double = pd.concat(
            [name_num, double_chn_science, double_math_science, double_eng_science, double_phys_science,
             double_chem_science, double_bio_science, double_total_science], axis=1)

        science_single.loc['理科共计'] = [science_single['参考人数'].sum(),
                                          science_single['语文'].sum(),
                                          science_single['数学'].sum(),
                                          science_single['英语'].sum(),
                                          science_single['物理'].sum(),
                                          science_single['化学'].sum(),
                                          science_single['生物'].sum(),
                                          science_single['总分'].sum()
                                          ]
        # 新增一列上线率
        science_single['上线率'] = science_single['总分'] / science_single['参考人数']
        science_single['上线率'] = science_single['上线率'].apply(lambda x: format(x, '.2%'))
        science_double.loc['理科共计'] = [science_double['参考人数'].sum(),
                                          science_double['语文'].sum(),
                                          science_double['数学'].sum(),
                                          science_double['英语'].sum(),
                                          science_double['物理'].sum(),
                                          science_double['化学'].sum(),
                                          science_double['生物'].sum(),
                                          science_double['总分'].sum()]
        good_scores_dict = {'参考人数': ' ', '语文': chn, '数学': math, '英语': eng,
                            '物理': phys, '化学': chem, '生物': bio, '总分': total}
        good_scores_science = pd.DataFrame(good_scores_dict, index=['班级'])

        return science_single, science_double, good_scores_science

    def contribution_shoot_arts(self):
        result_single, result_double, good_scores = self.single_double_arts(*ScoreAnalysis.arts_scores)
        shoot_dict = {'语文': result_double['语文'] / result_single['语文'],
                      '数学': result_double['数学'] / result_single['数学'],
                      '英语': result_double['英语'] / result_single['英语'],
                      '政治': result_double['政治'] / result_single['政治'],
                      '历史': result_double['历史'] / result_single['历史'],
                      '地理': result_double['地理'] / result_single['地理']}
        shoot_df = pd.DataFrame(shoot_dict)
        contribution_dict = {'语文': result_double['语文'] / result_double['总分'],
                             '数学': result_double['数学'] / result_double['总分'],
                             '英语': result_double['英语'] / result_double['总分'],
                             '政治': result_double['政治'] / result_double['总分'],
                             '历史': result_double['历史'] / result_double['总分'],
                             '地理': result_double['地理'] / result_double['总分']}
        contribution_df = pd.DataFrame(contribution_dict)
        return shoot_df, contribution_df

    def contribution_shoot_science(self):
        result_single, result_double, good_score_science = self.single_double_science(*ScoreAnalysis.science_scores)
        shoot_dict = {'语文': result_double['语文'] / result_single['语文'],
                      '数学': result_double['数学'] / result_single['数学'],
                      '英语': result_double['英语'] / result_single['英语'],
                      '物理': result_double['物理'] / result_single['物理'],
                      '化学': result_double['化学'] / result_single['化学'],
                      '生物': result_double['生物'] / result_single['生物'],
                      }
        shoot_df = pd.DataFrame(shoot_dict)
        contribution_dict = {'语文': result_double['语文'] / result_double['总分'],
                             '数学': result_double['数学'] / result_double['总分'],
                             '英语': result_double['英语'] / result_double['总分'],
                             '物理': result_double['物理'] / result_double['总分'],
                             '化学': result_double['化学'] / result_double['总分'],
                             '生物': result_double['生物'] / result_double['总分'],
                             }
        contribution_df = pd.DataFrame(contribution_dict)
        return shoot_df, contribution_df

    def subject_grade_arts(self):

        result_single, result_double, good_scores = self.single_double_arts(*ScoreAnalysis.arts_scores)
        shoot_df, contribution_df = self.contribution_shoot_arts()
        result_single.fillna(0, inplace=True)
        result_double.fillna(0, inplace=True)
        shoot_df.fillna(0, inplace=True)
        contribution_df.fillna(0, inplace=True)
        grade = pd.DataFrame(columns=shoot_df.columns, index=shoot_df.index)

        def grade_assess(subject):
            for i in shoot_df.index:
                if result_single['总分'].at[i] != 0:
                    if (result_single[subject].at[i]) >= (result_single['总分'].at[i]) * 0.8:
                        if (contribution_df[subject].at[i] >= 0.7) & (shoot_df[subject].at[i] >= 0.6):
                            grade[subject].at[i] = 'A'
                        elif (contribution_df[subject].at[i] >= 0.7) & (shoot_df[subject].at[i] < 0.6):
                            grade[subject].at[i] = 'B'
                        elif (contribution_df[subject].at[i] < 0.7) & (shoot_df[subject].at[i] >= 0.6):
                            grade[subject].at[i] = 'C'
                        else:
                            grade[subject].at[i] = 'D'
                    else:
                        grade[subject].at[i] = 'E'
                else:
                    grade[subject].at[i] = 'F'

        grade_assess('语文')
        grade_assess('数学')
        grade_assess('英语')
        grade_assess('政治')
        grade_assess('历史')
        grade_assess('地理')

        contribution_df = contribution_df.applymap(lambda x: format(x, '.2%'))
        shoot_df = shoot_df.applymap(lambda x: format(x, '.2%'))
        final_grade = pd.concat([good_scores, result_single, result_double, contribution_df, shoot_df, grade],
                                keys=['有效分', '单有效', '双有效', '贡献率', '命中率', '等级'])
        return final_grade

    def subject_grade_science(self):
        result_single, result_double, good_score_science = self.single_double_science(*ScoreAnalysis.science_scores)
        shoot_df, contribution_df = self.contribution_shoot_science()
        result_single.fillna(0, inplace=True)
        result_double.fillna(0, inplace=True)
        shoot_df.fillna(0, inplace=True)
        contribution_df.fillna(0, inplace=True)
        grade = pd.DataFrame(columns=shoot_df.columns, index=shoot_df.index)

        def grade_assess(subject):
            for i in shoot_df.index:
                if result_single['总分'].at[i] != 0:
                    if (result_single[subject].at[i]) >= (result_single['总分'].at[i]) * 0.8:
                        if (contribution_df[subject].at[i] >= 0.7) & (shoot_df[subject].at[i] >= 0.6):
                            grade[subject].at[i] = 'A'
                        elif (contribution_df[subject].at[i] >= 0.7) & (shoot_df[subject].at[i] < 0.6):
                            grade[subject].at[i] = 'B'
                        elif (contribution_df[subject].at[i] < 0.7) & (shoot_df[subject].at[i] >= 0.6):
                            grade[subject].at[i] = 'C'
                        else:
                            grade[subject].at[i] = 'D'
                    else:
                        grade[subject].at[i] = 'E'
                else:
                    grade[subject].at[i] = 'F'

        grade_assess('语文')
        grade_assess('数学')
        grade_assess('英语')
        grade_assess('物理')
        grade_assess('化学')
        grade_assess('生物')
        # print(grade_assess('化学'))

        contribution_df = contribution_df.applymap(lambda x: format(x, '.2%'))
        shoot_df = shoot_df.applymap(lambda x: format(x, '.2%'))

        final_grade = pd.concat([good_score_science, result_single, result_double, contribution_df, shoot_df, grade],
                                keys=['有效分', '单有效', '双有效', '贡献率', '命中率', '等级'])
        return final_grade

    def goodscore_arts(self, chn, math, eng, pol, his, geo, total):
        """
        计算文科各科各班单有效和双有效人数
        """
        single_chn_arts, double_chn_arts = self.get_single_double_score(self.df_arts, '语文', chn, total)
        single_math_arts, double_math_arts = self.get_single_double_score(self.df_arts, '数学', math, total)
        single_eng_arts, double_eng_arts = self.get_single_double_score(self.df_arts, '英语', eng, total)
        single_pol_arts, double_pol_arts = self.get_single_double_score(self.df_arts, '政治', pol, total)
        single_his_arts, double_his_arts = self.get_single_double_score(self.df_arts, '历史', his, total)
        single_geo_arts, double_geo_arts = self.get_single_double_score(self.df_arts, '地理', geo, total)
        single_total_arts, double_total_arts = self.get_single_double_score(self.df_arts, '总分', total, total)

        name_num = self.df_arts.groupby(['班级'])['姓名'].count()
        name_num.name = '参考人数'

        goodscore_dict = {'参考人数': ' ', '语文': chn, '数学': math, '英语': eng, '政治': pol, '历史': his,
                          '地理': geo, '总分': total}
        goodscore_df = pd.DataFrame(goodscore_dict, index=['班级'])

        result_single = pd.concat([name_num, single_chn_arts, single_math_arts, single_eng_arts,
                                   single_pol_arts, single_his_arts, single_geo_arts, single_total_arts],
                                  axis=1)
        result_double = pd.concat(
            [name_num, double_chn_arts, double_math_arts, double_eng_arts,
             double_pol_arts, double_his_arts, double_geo_arts, double_total_arts], axis=1)

        result_single.loc['文科共计'] = [result_single['参考人数'].sum(),
                                         result_single['语文'].sum(),
                                         result_single['数学'].sum(),
                                         result_single['英语'].sum(),
                                         result_single['政治'].sum(),
                                         result_single['历史'].sum(),
                                         result_single['地理'].sum(),
                                         result_single['总分'].sum()
                                         ]
        # 增加一列上线率
        # result_single['上线率'] = result_single['总分'] / result_single['参考人数']
        # result_single['上线率'] = result_single['上线率'].apply(lambda x: format(x, '.2%'))
        result_single = self.good_scores_ratio(result_single, '总分')
        result_double.loc['文科共计'] = [result_double['参考人数'].sum(),
                                         result_double['语文'].sum(),
                                         result_double['数学'].sum(),
                                         result_double['英语'].sum(),
                                         result_double['政治'].sum(),
                                         result_double['历史'].sum(),
                                         result_double['地理'].sum(),
                                         result_double['总分'].sum()]

        # 计算错位生人数
        unmatched_dict = {'参考人数': name_num, '语文': single_total_arts - double_chn_arts,
                          '数学': single_total_arts - double_math_arts, '英语': single_total_arts - double_eng_arts,
                          '政治': single_total_arts - double_pol_arts, '历史': single_total_arts - double_his_arts,
                          '地理': single_total_arts - double_geo_arts, '总分': single_total_arts - double_total_arts}
        unmatched_df = pd.DataFrame(unmatched_dict)
        # 新增一行共计
        unmatched_df.loc['共计'] = [unmatched_df['参考人数'].sum(),
                                    unmatched_df['语文'].sum(),
                                    unmatched_df['数学'].sum(),
                                    unmatched_df['英语'].sum(),
                                    unmatched_df['政治'].sum(),
                                    unmatched_df['历史'].sum(),
                                    unmatched_df['地理'].sum(),
                                    unmatched_df['总分'].sum()
                                    ]
        result_final_arts = pd.concat([goodscore_df, result_single, result_double, unmatched_df], axis=0,
                                      keys=['有效分数', '单有效', '双有效', '错位数'])
        # 计算学科贡献率，命中率和等级评定
        shoot_df, contribution_df = self.contribution_shoot_arts()
        result_single.fillna(0, inplace=True)
        result_double.fillna(0, inplace=True)
        shoot_df.fillna(0, inplace=True)
        contribution_df.fillna(0, inplace=True)
        grade = pd.DataFrame(columns=['语文', '数学', '英语', '政治', '历史', '地理'], index=shoot_df.index)

        def grade_assess(subject):
            for i in shoot_df.index:
                if result_single['总分'].at[i] != 0:
                    if (result_single[subject].at[i]) >= (result_single['总分'].at[i]) * 0.8:
                        if (contribution_df[subject].at[i] >= 0.7) & (shoot_df[subject].at[i] >= 0.6):
                            grade[subject].at[i] = 'A'
                        elif (contribution_df[subject].at[i] >= 0.7) & (shoot_df[subject].at[i] < 0.6):
                            grade[subject].at[i] = 'B'
                        elif (contribution_df[subject].at[i] < 0.7) & (shoot_df[subject].at[i] >= 0.6):
                            grade[subject].at[i] = 'C'
                        else:
                            grade[subject].at[i] = 'D'
                    else:
                        grade[subject].at[i] = 'E'
                else:
                    grade[subject].at[i] = 'F'

        grade_assess('语文')
        grade_assess('数学')
        grade_assess('英语')
        grade_assess('政治')
        grade_assess('历史')
        grade_assess('地理')
        contribution_df = contribution_df.applymap(lambda x: format(x, '.2%'))
        shoot_df = shoot_df.applymap(lambda x: format(x, '.2%'))
        final_grade = pd.concat(
            [goodscore_df, result_single, result_double, unmatched_df, contribution_df, shoot_df, grade],
            keys=['有效分数', '单有效', '双有效', '错位数', '贡献率', '命中率', '等级'])

        return result_final_arts, final_grade

    def goodscore_science(self, chn, math, eng, phys, chem, bio, total):
        """
        计算理科各科各班上单有效和双有效分人数
        """
        single_chn_science, double_chn_science = self.get_single_double_score(self.df_science, '语文', chn, total)
        single_math_science, double_math_science = self.get_single_double_score(self.df_science, '数学', math, total)
        single_eng_science, double_eng_science = self.get_single_double_score(self.df_science, '英语', eng, total)
        single_phys_science, double_phys_science = self.get_single_double_score(self.df_science, '物理', phys, total)
        single_chem_science, double_chem_science = self.get_single_double_score(self.df_science, '化学', chem, total)
        single_bio_science, double_bio_science = self.get_single_double_score(self.df_science, '生物', bio, total)
        single_total_science, double_total_science = self.get_single_double_score(self.df_science, '总分', total, total)

        name_num = self.df_science.groupby(['班级'])['姓名'].count()
        name_num.name = '参考人数'

        goodscore_dict = {'参考人数': ' ', '语文': chn, '数学': math, '英语': eng, '物理': phys,
                          '化学': chem, '生物': bio, '总分': total}
        goodscore_df = pd.DataFrame(goodscore_dict, index=['有效分数'])

        result_single = pd.concat([name_num, single_chn_science, single_math_science, single_eng_science,
                                   single_phys_science, single_chem_science, single_bio_science, single_total_science],
                                  axis=1)
        result_double = pd.concat(
            [name_num, double_chn_science, double_math_science, double_eng_science, double_phys_science,
             double_chem_science, double_bio_science, double_total_science], axis=1)

        result_single.loc['理科共计'] = [result_single['参考人数'].sum(),
                                         result_single['语文'].sum(),
                                         result_single['数学'].sum(),
                                         result_single['英语'].sum(),
                                         result_single['物理'].sum(),
                                         result_single['化学'].sum(),
                                         result_single['生物'].sum(),
                                         result_single['总分'].sum()
                                         ]
        # 新增一列上线率
        result_single = self.good_scores_ratio(result_single, '总分')

        result_double.loc['理科共计'] = [result_double['参考人数'].sum(),
                                         result_double['语文'].sum(),
                                         result_double['数学'].sum(),
                                         result_double['英语'].sum(),
                                         result_double['物理'].sum(),
                                         result_double['化学'].sum(),
                                         result_double['生物'].sum(),
                                         result_double['总分'].sum()]

        # 计算错位生人数
        unmatched_dict = {'参考人数': name_num, '语文': single_total_science - double_chn_science,
                          '数学': single_total_science - double_math_science,
                          '英语': single_total_science - double_eng_science,
                          '物理': single_total_science - double_phys_science,
                          '化学': single_total_science - double_chem_science,
                          '生物': single_total_science - double_bio_science, '总分': single_total_science}
        unmatched_df = pd.DataFrame(unmatched_dict)
        unmatched_df.loc['共计'] = [unmatched_df['参考人数'].sum(),
                                    unmatched_df['语文'].sum(),
                                    unmatched_df['数学'].sum(),
                                    unmatched_df['英语'].sum(),
                                    unmatched_df['物理'].sum(),
                                    unmatched_df['化学'].sum(),
                                    unmatched_df['生物'].sum(),
                                    unmatched_df['总分'].sum()]
        result_final_science = pd.concat([goodscore_df, result_single, result_double, unmatched_df], axis=0,
                                         keys=['有效分数', '单有效', '双有效', '错位数'])
        result_final_science.fillna(0, inplace=True)

        # 计算学科贡献率，命中率和等级评定
        shoot_dict = {'语文': result_double['语文'] / result_single['语文'],
                      '数学': result_double['数学'] / result_single['数学'],
                      '英语': result_double['英语'] / result_single['英语'],
                      '物理': result_double['物理'] / result_single['物理'],
                      '化学': result_double['化学'] / result_single['化学'],
                      '生物': result_double['生物'] / result_single['生物']}
        shoot_df = pd.DataFrame(shoot_dict)
        contribution_dict = {'语文': result_double['语文'] / result_double['总分'],
                             '数学': result_double['数学'] / result_double['总分'],
                             '英语': result_double['英语'] / result_double['总分'],
                             '物理': result_double['物理'] / result_double['总分'],
                             '化学': result_double['化学'] / result_double['总分'],
                             '生物': result_double['生物'] / result_double['总分']}
        contribution_df = pd.DataFrame(contribution_dict)
        result_single.fillna(0, inplace=True)
        result_double.fillna(0, inplace=True)
        shoot_df.fillna(0, inplace=True)
        contribution_df.fillna(0, inplace=True)
        grade = pd.DataFrame(columns=['语文', '数学', '英语', '物理', '化学', '生物'], index=shoot_df.index)

        def grade_assess(subject):
            for i in shoot_df.index:
                if result_single['总分'].at[i] != 0:
                    if (result_single[subject].at[i]) >= (result_single['总分'].at[i]) * 0.8:
                        if (contribution_df[subject].at[i] >= 0.7) & (shoot_df[subject].at[i] >= 0.6):
                            grade[subject].at[i] = 'A'
                        elif (contribution_df[subject].at[i] >= 0.7) & (shoot_df[subject].at[i] < 0.6):
                            grade[subject].at[i] = 'B'
                        elif (contribution_df[subject].at[i] < 0.7) & (shoot_df[subject].at[i] >= 0.6):
                            grade[subject].at[i] = 'C'
                        else:
                            grade[subject].at[i] = 'D'
                    else:
                        grade[subject].at[i] = 'E'
                else:
                    grade[subject].at[i] = 'F'

        grade_assess('语文')
        grade_assess('数学')
        grade_assess('英语')
        grade_assess('物理')
        grade_assess('化学')
        grade_assess('生物')
        contribution_df = contribution_df.applymap(lambda x: format(x, '.2%'))
        shoot_df = shoot_df.applymap(lambda x: format(x, '.2%'))
        final_grade = pd.concat(
            [goodscore_df, result_single, result_double, unmatched_df, contribution_df, shoot_df, grade],
            keys=['有效分数', '单有效', '双有效', '错位数', '贡献率', '命中率', '等级'])
        return result_final_science, final_grade

    def get_unmatched_arts(self, chn, math, eng, pol, his, geo, total):

        df_chn = self.get_unmatched_students(self.df_arts, '语文', chn, total)
        df_math = self.get_unmatched_students(self.df_arts, '数学', math, total)
        df_eng = self.get_unmatched_students(self.df_arts, '英语', eng, total)
        df_pol = self.get_unmatched_students(self.df_arts, '政治', pol, total)
        df_his = self.get_unmatched_students(self.df_arts, '历史', his, total)
        df_geo = self.get_unmatched_students(self.df_arts, '地理', geo, total)
        df_unmatched_list = pd.concat([df_chn, df_math, df_eng, df_pol, df_his, df_geo], axis=1)

        df_cnh_num = df_chn.groupby('班级')[['语文']].count()
        df_math_num = df_math.groupby('班级')[['数学']].count()
        df_eng_num = df_eng.groupby('班级')[['英语']].count()
        df_pol_num = df_pol.groupby('班级')[['政治']].count()
        df_his_num = df_his.groupby('班级')[['历史']].count()
        df_geo_num = df_geo.groupby('班级')[['地理']].count()
        df_unmatched_num = pd.concat([df_cnh_num, df_math_num, df_eng_num, df_pol_num, df_his_num, df_geo_num], axis=1)
        return df_unmatched_num, df_unmatched_list

    def get_unmatched_science(self, chn, math, eng, phys, chem, bio, total):
        # 获取各科错位生名单
        df_chn = self.get_unmatched_students(self.df_science, '语文', chn, total)
        df_math = self.get_unmatched_students(self.df_science, '数学', math, total)
        df_eng = self.get_unmatched_students(self.df_science, '英语', eng, total)
        df_phys = self.get_unmatched_students(self.df_science, '物理', phys, total)
        df_chem = self.get_unmatched_students(self.df_science, '化学', chem, total)
        df_bio = self.get_unmatched_students(self.df_science, '生物', bio, total)
        df_unmatched_list = pd.concat([df_chn, df_math, df_eng, df_phys, df_chem, df_bio], axis=1)
        # 计算各科错位人数
        df_cnh_num = df_chn.groupby('班级')[['语文']].count()
        df_math_num = df_math.groupby('班级')[['数学']].count()
        df_eng_num = df_eng.groupby('班级')[['英语']].count()
        df_phys_num = df_phys.groupby('班级')[['物理']].count()
        df_chem_num = df_chem.groupby('班级')[['化学']].count()
        df_bio_num = df_bio.groupby('班级')[['生物']].count()
        df_unmatched_num = pd.concat([df_cnh_num, df_math_num, df_eng_num, df_phys_num, df_chem_num, df_bio_num],
                                     axis=1)
        return df_unmatched_num, df_unmatched_list

    def line_betweens(self, total=None, total_science=None):
        self.class_rank()
        line_condition = (self.df_arts['总分'] >= total - 20) & (self.df_arts['总分'] <= total + 20)
        line_condition_science = (self.df_science['总分'] >= total_science - 20) & (
                self.df_science['总分'] <= total_science + 20)
        df_line_arts = self.df_arts.loc[line_condition, :]
        df_line_science = self.df_science.loc[line_condition_science, :]
        writer = pd.ExcelWriter(r'D:\成绩统计结果\本次考试踩线生分班名单.xlsx')
        class_num = list(df_line_arts['班级'].drop_duplicates())
        class_num_science = list(df_line_science['班级'].drop_duplicates())
        for i in class_num:
            class_name = df_line_arts[df_line_arts['班级'] == i].reset_index(drop=True)
            class_name['序号'] = [k + 1 for k in class_name.index]
            class_name = class_name.loc[:, ['序号', '姓名', '班级', '语文', '数学', '英语',
                                            '政治', '历史', '地理', '总分', '排名']]
            class_name.to_excel(writer, sheet_name=i, index=False)

        for i in class_num_science:
            class_name = df_line_science[df_line_science['班级'] == i].reset_index(drop=True)
            class_name['序号'] = [k + 1 for k in class_name.index]
            class_name = class_name.loc[:, ['序号', '姓名', '班级', '语文', '数学', '英语',
                                            '物理', '化学', '生物', '总分', '排名']]
            class_name.to_excel(writer, sheet_name=i, index=False)

        writer.close()

    def class_divided(self):
        """
        计算获得文理科各班成绩表
        :return:
        """
        self.class_rank()
        class_No_arts = list(self.df_arts['班级'].drop_duplicates())
        class_NO_science = list(self.df_science['班级'].drop_duplicates())
        writer = pd.ExcelWriter(r'D:\成绩统计结果\本次考试文理各班成绩表.xlsx')
        for i in class_No_arts:
            class_arts = self.df_arts[self.df_arts['班级'] == i].reset_index(drop=True)
            class_arts['序号'] = [k + 1 for k in class_arts.index]
            class_arts['综合'] = class_arts['政治'] + class_arts['历史'] + class_arts['地理']
            class_arts = class_arts.loc[:, ['序号', '班级', '姓名', '语文', '数学', '英语', '综合',
                                            '政治', '历史', '地理', '总分', '排名']]
            class_arts.to_excel(writer, sheet_name=i, index=False)
        for i in class_NO_science:
            class_science = self.df_science[self.df_science['班级'] == i].reset_index(drop=True)
            class_science['序号'] = [k + 1 for k in class_science.index]
            class_science['综合'] = class_science['物理'] + class_science['化学'] + class_science['生物']
            class_science = class_science.loc[:, ['序号', '班级', '姓名', '语文', '数学', '英语', '综合',
                                                  '物理', '化学', '生物', '总分', '排名']]
            class_science.to_excel(writer, sheet_name=i, index=False)
        writer.close()

    def class_rank(self):
        """
        计算文理科学生排名
        :return:
        """
        self.df_arts['排名'] = self.df_arts['总分'].rank(method='min', ascending=False)
        # self.df_arts['排名'] = self.df_arts['排名'].apply(lambda x: format(int(x)))
        self.df_arts.sort_values(by='总分', ascending=False, inplace=True)

        self.df_science['排名'] = self.df_science['总分'].rank(method='min', ascending=False)
        # self.df_science['排名'] = self.df_science['排名'].apply(lambda x: format(int(x)))
        self.df_science.sort_values(by='总分', ascending=False, inplace=True)

    def score_label(self):
        """
        计算打印考生个人成绩单
        """
        self.class_rank()
        exam_arts = self.df_arts.loc[:,
                    ['班级', '姓名', '语文', '数学', '英语', '政治', '历史', '地理', '总分', '排名']]
        exam_science = self.df_science.loc[:,
                       ['班级', '姓名', '语文', '数学', '英语', '物理', '化学', '生物', '总分', '排名']]
        exam_arts.sort_values(by=['班级', '总分'], inplace=True, ascending=[True, False], ignore_index=True)
        exam_science.sort_values(by=['班级', '总分'], inplace=True, ascending=[True, False], ignore_index=True)

        for i in exam_arts.index:
            exam_arts.loc[i + 0.5] = exam_arts.columns
        exam_arts.sort_index(inplace=True, ignore_index=True)
        for i in exam_science.index:
            exam_science.loc[i + 0.5] = exam_science.columns
        exam_science.sort_index(inplace=True)
        with pd.ExcelWriter('本次考试学生个人成绩单.xlsx') as writer:
            exam_arts.to_excel(writer, sheet_name='文科成绩单', index=False)
            exam_science.to_excel(writer, sheet_name='理科成绩单', index=False)

    def arts_science_combined(self):
        arts_av, science_av = self.get_av()
        arts, grades_arts = self.goodscore_arts(*ScoreAnalysis.arts_scores)
        science, grades_science = self.goodscore_science(*ScoreAnalysis.science_scores)
        arts_av = self.write_open(arts_av)
        science_av = self.write_open(science_av)
        grades_arts = self.write_open(grades_arts)
        grades_science = self.write_open(grades_science)
        arts_science = pd.concat([grades_arts, grades_science, arts_av, science_av], ignore_index=True)
        # with pd.ExcelWriter(r'D:\成绩统计结果\文理有效分统计分析.xlsx') as writer:
        #     arts_science.to_excel(writer, sheet_name='文理有效分', index=False)
        result_file = self.file_name_by_time(arts_science)
        # print(result_file)
        return result_file

    def arts_science_combined_school(self, goodtotal_arts=None, goodtotal_science=None):
        chn, math, eng, pol, his, geo, total = self.get_goodscores_arts(goodtotal_arts)
        chn_science, math_science, eng_science, phys, chem, bio, total_science = self.get_goodscores_science(
            goodtotal_science)
        arts_av, science_av = self.get_av()

        arts, grades_arts = self.goodscore_arts(chn, math, eng, pol, his, geo, total)
        science, grades_science = self.goodscore_science(chn_science, math_science, eng_science,
                                                         phys, chem, bio,
                                                         total_science)
        arts_av = self.write_open(arts_av)
        science_av = self.write_open(science_av)
        grades_arts = self.write_open(grades_arts)
        grades_science = self.write_open(grades_science)
        arts_science = pd.concat([grades_arts, grades_science, arts_av, science_av])

        # with pd.ExcelWriter(r'D:\成绩统计结果\文理有效分统计分析.xlsx') as writer:
        #     arts_science.to_excel(writer, sheet_name='文理有效分', index=False)
        # result_file = '文理有效分统计分析.xlsx'
        result_file = self.file_name_by_time(arts_science)
        return result_file

    def good_scores_arts_ratio(self, chn, math, eng, pol, his, geo, total):
        # 计算各班单有效学生人数
        single_chn_arts = self.df_arts[self.df_arts['语文'] >= chn].groupby(['班级'])['语文'].count()
        single_math_arts = self.df_arts[self.df_arts['数学'] >= math].groupby(['班级'])['数学'].count()
        single_eng_arts = self.df_arts[self.df_arts['英语'] >= eng].groupby(['班级'])['英语'].count()
        single_pol_arts = self.df_arts[self.df_arts['政治'] >= pol].groupby(['班级'])['政治'].count()
        single_his_arts = self.df_arts[self.df_arts['历史'] >= his].groupby(['班级'])['历史'].count()
        single_geo_arts = self.df_arts[self.df_arts['地理'] >= geo].groupby(['班级'])['地理'].count()
        single_total_arts = self.df_arts[self.df_arts['总分'] >= total].groupby(['班级'])['总分'].count()
        # 计算参考人数
        name_num = self.df_arts.groupby(['班级'])['姓名'].count()
        name_num.name = '参考人数'
        # 计算双有效各班学生人数
        df2 = self.df_arts[self.df_arts['总分'] >= total]
        double_chn_arts = df2[df2['语文'] >= chn].groupby(['班级'])['语文'].count()
        double_math_arts = df2[df2['数学'] >= math].groupby(['班级'])['数学'].count()
        double_eng_arts = df2[df2['英语'] >= eng].groupby(['班级'])['英语'].count()
        double_pol_arts = df2[df2['政治'] >= pol].groupby(['班级'])['政治'].count()
        double_his_arts = df2[df2['历史'] >= his].groupby(['班级'])['历史'].count()
        double_geo_arts = df2[df2['地理'] >= geo].groupby(['班级'])['地理'].count()
        double_total_arts = df2[df2['总分'] >= total].groupby(['班级'])['总分'].count()

        # goodscore_dict = {'参考人数': ' ', '语文': chn, '数学': math, '英语': eng, '政治': pol, '历史': his, '地理': geo, '总分': total}
        # goodscore_df = pd.DataFrame(goodscore_dict, index=['有效分数'])

        result_single = pd.concat([name_num, single_chn_arts, single_math_arts, single_eng_arts,
                                   single_pol_arts, single_his_arts, single_geo_arts, single_total_arts],
                                  axis=1)

        result_double = pd.concat(
            [name_num, double_chn_arts, double_math_arts, double_eng_arts,
             double_pol_arts, double_his_arts, double_geo_arts, double_total_arts], axis=1)

        result_single.loc['文科共计'] = [result_single['参考人数'].sum(),
                                         result_single['语文'].sum(),
                                         result_single['数学'].sum(),
                                         result_single['英语'].sum(),
                                         result_single['政治'].sum(),
                                         result_single['历史'].sum(),
                                         result_single['地理'].sum(),
                                         result_single['总分'].sum()
                                         ]
        # 新增上线率一列并用百分数表示
        # result_single['上线率'] = result_single['总分'] / result_single['参考人数']
        # result_single['上线率'] = result_single['上线率'].apply(lambda x: format(x, '.2%'))
        result_single = self.good_scores_ratio(result_single, '总分')
        result_single = self.good_scores_ratio(result_single, '语文')
        result_single = self.good_scores_ratio(result_single, '数学')
        result_single = self.good_scores_ratio(result_single, '英语')
        result_single = self.good_scores_ratio(result_single, '政治')
        result_single = self.good_scores_ratio(result_single, '历史')
        result_single = self.good_scores_ratio(result_single, '地理')
        order = ['参考人数', '语文', '语文上线率', '数学', '数学上线率', '英语', '英语上线率',
                 '政治', '政治上线率', '历史', '历史上线率', '地理', '地理上线率', '总分', '总分上线率']
        result_single = result_single[order]
        result_double.loc['文科共计'] = [result_double['参考人数'].sum(),
                                         result_double['语文'].sum(),
                                         result_double['数学'].sum(),
                                         result_double['英语'].sum(),
                                         result_double['政治'].sum(),
                                         result_double['历史'].sum(),
                                         result_double['地理'].sum(),
                                         result_double['总分'].sum()]
        result_double = self.good_scores_ratio(result_double, '总分')
        result_double = self.good_scores_ratio(result_double, '语文')
        result_double = self.good_scores_ratio(result_double, '数学')
        result_double = self.good_scores_ratio(result_double, '英语')
        result_double = self.good_scores_ratio(result_double, '政治')
        result_double = self.good_scores_ratio(result_double, '历史')
        result_double = self.good_scores_ratio(result_double, '地理')
        order = ['参考人数', '语文', '语文上线率', '数学', '数学上线率', '英语', '英语上线率',
                 '政治', '政治上线率', '历史', '历史上线率', '地理', '地理上线率', '总分', '总分上线率']
        result_double = result_double[order]
        single_double_concat = pd.concat([result_single, result_double], keys=['单有效', '双有效'])

        return single_double_concat

    def good_scores_science_ratio(self, chn, math, eng, pol, his, geo, total):

        single_chn_science = self.df_science[self.df_science['语文'] >= chn].groupby(['班级'])['语文'].count()
        single_math_science = self.df_science[self.df_science['数学'] >= math].groupby(['班级'])['数学'].count()
        single_eng_science = self.df_science[self.df_science['英语'] >= eng].groupby(['班级'])['英语'].count()
        single_phys_science = self.df_science[self.df_science['物理'] >= pol].groupby(['班级'])['物理'].count()
        single_chem_science = self.df_science[self.df_science['化学'] >= his].groupby(['班级'])['化学'].count()
        single_bio_science = self.df_science[self.df_science['生物'] >= geo].groupby(['班级'])['生物'].count()
        single_total_science = self.df_science[self.df_science['总分'] >= total].groupby(['班级'])['总分'].count()

        name_num = self.df_science.groupby(['班级'])['姓名'].count()
        name_num.name = '参考人数'

        df2 = self.df_science[self.df_science['总分'] >= total]
        double_chn_science = df2[df2['语文'] >= chn].groupby(['班级'])['语文'].count()
        double_math_science = df2[df2['数学'] >= math].groupby(['班级'])['数学'].count()
        double_eng_science = df2[df2['英语'] >= eng].groupby(['班级'])['英语'].count()
        double_phys_science = df2[df2['物理'] >= pol].groupby(['班级'])['物理'].count()
        double_chem_science = df2[df2['化学'] >= his].groupby(['班级'])['化学'].count()
        double_bio_science = df2[df2['生物'] >= geo].groupby(['班级'])['生物'].count()
        double_total__science = df2[df2['总分'] >= total].groupby(['班级'])['总分'].count()

        result_single = pd.concat([name_num, single_chn_science, single_math_science, single_eng_science,
                                   single_phys_science, single_chem_science, single_bio_science, single_total_science],
                                  axis=1)
        result_double = pd.concat(
            [name_num, double_chn_science, double_math_science, double_eng_science, double_phys_science,
             double_chem_science, double_bio_science, double_total__science], axis=1)

        result_single.loc['理科共计'] = [result_single['参考人数'].sum(),
                                         result_single['语文'].sum(),
                                         result_single['数学'].sum(),
                                         result_single['英语'].sum(),
                                         result_single['物理'].sum(),
                                         result_single['化学'].sum(),
                                         result_single['生物'].sum(),
                                         result_single['总分'].sum()
                                         ]
        # result_single['上线率'] = result_single['总分'] / result_single['参考人数']
        # result_single['上线率'] = result_single['上线率'].apply(lambda x: format(x, '.2%'))

        result_single = self.good_scores_ratio(result_single, '总分')
        result_single = self.good_scores_ratio(result_single, '语文')
        result_single = self.good_scores_ratio(result_single, '数学')
        result_single = self.good_scores_ratio(result_single, '英语')
        result_single = self.good_scores_ratio(result_single, '物理')
        result_single = self.good_scores_ratio(result_single, '化学')
        result_single = self.good_scores_ratio(result_single, '生物')
        order = ['参考人数', '语文', '语文上线率', '数学', '数学上线率', '英语', '英语上线率',
                 '物理', '物理上线率', '化学', '化学上线率', '生物', '生物上线率', '总分', '总分上线率']
        result_single = result_single[order]

        result_double.loc['理科共计'] = [result_double['参考人数'].sum(),
                                         result_double['语文'].sum(),
                                         result_double['数学'].sum(),
                                         result_double['英语'].sum(),
                                         result_double['物理'].sum(),
                                         result_double['化学'].sum(),
                                         result_double['生物'].sum(),
                                         result_double['总分'].sum()]
        result_double = self.good_scores_ratio(result_double, '总分')
        result_double = self.good_scores_ratio(result_double, '语文')
        result_double = self.good_scores_ratio(result_double, '数学')
        result_double = self.good_scores_ratio(result_double, '英语')
        result_double = self.good_scores_ratio(result_double, '物理')
        result_double = self.good_scores_ratio(result_double, '化学')
        result_double = self.good_scores_ratio(result_double, '生物')
        order = ['参考人数', '语文', '语文上线率', '数学', '数学上线率', '英语', '英语上线率',
                 '物理', '物理上线率', '化学', '化学上线率', '生物', '生物上线率', '总分', '总分上线率']
        result_double = result_double[order]
        single_double_concat_science = pd.concat([result_single, result_double], keys=['单有效', '双有效'])

        return single_double_concat_science

    @staticmethod
    def file_name_by_time(data_df):
        file_time = time.time()
        file_name = f'{file_time}.xlsx'
        data_df.to_excel('/data/results/' + file_name, sheet_name='成绩分析', index=False)
        return file_name

    @staticmethod
    def good_scores_ratio(data, subject):
        data[subject + '上线率'] = data[subject] / data['参考人数']
        data[subject + '上线率'] = data[subject + '上线率'].apply(lambda x: format(x, '.1%'))
        return data

    @staticmethod
    def get_subject_good_score(data, subject, total):
        """
        获取各科有效分
        :param data: df数据
        :param subject: 学科名
        :param total: 上线总分
        :return: 学科有效分
        """
        good_score_data = data.loc[data['总分'] >= total]
        subject_av = good_score_data[subject].mean()
        total_av = good_score_data['总分'].mean()
        subject_good_score = round(subject_av * total / total_av)
        return subject_good_score

    @staticmethod
    def get_single_double_score(data, subject, subject_score, total_score):
        """
        get the good scores of a subject in an exam
        :param data:
        :param subject:
        :param subject_score:
        :param total_score:
        :return:
        """
        single = data[data[subject] >= subject_score].groupby(['班级'])[subject].count()
        data_double = data[data['总分'] >= total_score]
        double = data_double[data_double[subject] >= subject_score].groupby(['班级'])[subject].count()
        return single, double

    @staticmethod
    def get_unmatched_students(data, subject, subject_score, total_score):
        """
        get the unmatched students in an exam
        :param data:
        :param subject:
        :param subject_score:
        :param total_score:
        :return:
        """
        df2 = data[data['总分'] >= total_score]
        df_unmatched_students = df2.loc[:, ['班级', '姓名', subject]].loc[df2[subject] < subject_score].sort_values(
            by=['班级', subject], ascending=[True, False]).reset_index(drop=True)
        return df_unmatched_students

    @staticmethod
    def write_open(df_data):
        df_data.to_excel('temp_data.xlsx')
        df_new = pd.read_excel('temp_data.xlsx', header=None)
        os.remove('temp_data.xlsx')
        return df_new

    @staticmethod
    def make_directory():
        if not os.path.exists('D:\\成绩统计结果'):
            os.makedirs('D:\\成绩统计结果')


class JuniorExam(object):

    def __init__(self, filepath):
        self.filepath = filepath
        self.df = pd.read_excel(self.filepath, sheet_name='总表')

    def junior_scores(self):
        """
        计算初中考试合格人数及合格率
        :return: 生成一个excel文件
        """
        if '化学' in self.df.columns:
            qualification_df = self.df.loc[:,
                               ['序号', '姓名', '班级', '语文A卷', '数学A卷', '英语A卷', '物理A卷', '化学', '总分']]
            # qualification_df.columns = ['序号', '姓名', '班级', '语文', '数学', '英语', '物理', '化学', '总分']

            # 计算合格人数
            single_chn = qualification_df[qualification_df['语文A卷'] >= 60].groupby(['班级'])['语文A卷'].count()
            single_math = qualification_df[qualification_df['数学A卷'] >= 60].groupby(['班级'])['数学A卷'].count()
            single_eng = qualification_df[qualification_df['英语A卷'] >= 60].groupby(['班级'])['英语A卷'].count()
            single_phys = qualification_df[qualification_df['物理A卷'] >= 60].groupby(['班级'])['物理A卷'].count()
            single_chem = qualification_df[qualification_df['化学'] >= 60].groupby(['班级'])['化学'].count()

            name_num = qualification_df.groupby(['班级'])['姓名'].count()
            name_num.name = '参考人数'

            qualified_data = pd.concat([name_num, single_chn, single_math, single_eng,
                                        single_phys, single_chem],
                                       axis=1)

            qualified_data.loc['年级'] = [qualified_data['参考人数'].sum(),
                                          qualified_data['语文A卷'].sum(),
                                          qualified_data['数学A卷'].sum(),
                                          qualified_data['英语A卷'].sum(),
                                          qualified_data['物理A卷'].sum(),
                                          qualified_data['化学'].sum()]
            # 计算合格率
            qualified_data['语文合格率'] = qualified_data['语文A卷'] / qualified_data['参考人数']
            qualified_data['语文合格率'] = qualified_data['语文合格率'].apply(lambda x: format(x, '.2%'))

            qualified_data['数学合格率'] = qualified_data['数学A卷'] / qualified_data['参考人数']
            qualified_data['数学合格率'] = qualified_data['数学合格率'].apply(lambda x: format(x, '.2%'))

            qualified_data['英语合格率'] = qualified_data['英语A卷'] / qualified_data['参考人数']
            qualified_data['英语合格率'] = qualified_data['英语合格率'].apply(lambda x: format(x, '.2%'))

            qualified_data['物理合格率'] = qualified_data['物理A卷'] / qualified_data['参考人数']
            qualified_data['物理合格率'] = qualified_data['物理合格率'].apply(lambda x: format(x, '.2%'))

            qualified_data['化学合格率'] = qualified_data['化学'] / qualified_data['参考人数']
            qualified_data['化学合格率'] = qualified_data['化学合格率'].apply(lambda x: format(x, '.2%'))

            qualified_data.reset_index(inplace=True)
            new_columns = ['班级', '参考人数', '语文A卷', '语文合格率', '数学A卷', '数学合格率', '英语A卷',
                           '英语合格率',
                           '物理A卷', '物理合格率', '化学', '化学合格率']
            qualified_data = qualified_data[new_columns]

            return qualified_data
        elif '物理A卷' not in self.df.columns:
            qualification_df = self.df.loc[:, ['序号', '姓名', '班级', '语文A卷', '数学A卷', '英语A卷', '总分']]
            # qualification_df.columns = ['序号', '姓名', '班级', '语文', '数学', '英语', '总分']

            # 计算合格人数
            single_chn = qualification_df[qualification_df['语文A卷'] >= 60].groupby(['班级'])['语文A卷'].count()
            single_math = qualification_df[qualification_df['数学A卷'] >= 60].groupby(['班级'])['数学A卷'].count()
            single_eng = qualification_df[qualification_df['英语A卷'] >= 60].groupby(['班级'])['英语A卷'].count()

            name_num = qualification_df.groupby(['班级'])['姓名'].count()
            name_num.name = '参考人数'

            qualified_data = pd.concat([name_num, single_chn, single_math, single_eng], axis=1)

            qualified_data.loc['年级'] = [qualified_data['参考人数'].sum(),
                                          qualified_data['语文A卷'].sum(),
                                          qualified_data['数学A卷'].sum(),
                                          qualified_data['英语A卷'].sum(),
                                          ]
            # 计算合格率
            qualified_data['语文合格率'] = qualified_data['语文A卷'] / qualified_data['参考人数']
            qualified_data['语文合格率'] = qualified_data['语文合格率'].apply(lambda x: format(x, '.2%'))

            qualified_data['数学合格率'] = qualified_data['数学A卷'] / qualified_data['参考人数']
            qualified_data['数学合格率'] = qualified_data['数学合格率'].apply(lambda x: format(x, '.2%'))

            qualified_data['英语合格率'] = qualified_data['英语A卷'] / qualified_data['参考人数']
            qualified_data['英语合格率'] = qualified_data['英语合格率'].apply(lambda x: format(x, '.2%'))

            qualified_data.reset_index(inplace=True)
            new_columns = ['班级', '参考人数', '语文A卷', '语文合格率', '数学A卷', '数学合格率', '英语A卷',
                           '英语合格率']
            qualified_data = qualified_data[new_columns]

            return qualified_data
        else:
            qualification_df = self.df.loc[:,
                               ['序号', '姓名', '班级', '语文A卷', '数学A卷', '英语A卷', '物理A卷', '总分']]
            # qualification_df.columns = ['序号', '姓名', '班级', '语文', '数学', '英语', '物理', '总分']

            # 计算合格人数
            single_chn = qualification_df[qualification_df['语文A卷'] >= 60].groupby(['班级'])['语文A卷'].count()
            single_math = qualification_df[qualification_df['数学A卷'] >= 60].groupby(['班级'])['数学A卷'].count()
            single_eng = qualification_df[qualification_df['英语A卷'] >= 60].groupby(['班级'])['英语A卷'].count()
            single_phys = qualification_df[qualification_df['物理A卷'] >= 60].groupby(['班级'])['物理A卷'].count()

            name_num = qualification_df.groupby(['班级'])['姓名'].count()
            name_num.name = '参考人数'

            qualified_data = pd.concat([name_num, single_chn, single_math, single_eng,
                                        single_phys], axis=1)

            qualified_data.loc['年级'] = [qualified_data['参考人数'].sum(),
                                          qualified_data['语文A卷'].sum(),
                                          qualified_data['数学A卷'].sum(),
                                          qualified_data['英语A卷'].sum(),
                                          qualified_data['物理A卷'].sum()]
            # 计算合格率
            qualified_data['语文合格率'] = qualified_data['语文A卷'] / qualified_data['参考人数']
            qualified_data['语文合格率'] = qualified_data['语文合格率'].apply(lambda x: format(x, '.2%'))

            qualified_data['数学合格率'] = qualified_data['数学A卷'] / qualified_data['参考人数']
            qualified_data['数学合格率'] = qualified_data['数学合格率'].apply(lambda x: format(x, '.2%'))

            qualified_data['英语合格率'] = qualified_data['英语A卷'] / qualified_data['参考人数']
            qualified_data['英语合格率'] = qualified_data['英语合格率'].apply(lambda x: format(x, '.2%'))

            qualified_data['物理合格率'] = qualified_data['物理A卷'] / qualified_data['参考人数']
            qualified_data['物理合格率'] = qualified_data['物理合格率'].apply(lambda x: format(x, '.2%'))

            qualified_data.reset_index(inplace=True)
            new_columns = ['班级', '参考人数', '语文A卷', '语文合格率', '数学A卷', '数学合格率', '英语A卷',
                           '英语合格率',
                           '物理A卷', '物理合格率']
            qualified_data = qualified_data[new_columns]

            return qualified_data

    def get_av(self):
        # 计算平均分
        if '化学' in self.df.columns:

            av_class = self.df.groupby(['班级'])[['语文A卷', '语文B卷', '语文合卷', '数学A卷', '数学B卷', '数学合卷',
                                                  '英语A卷', '英语B卷', '英语合卷', '物理A卷', '物理B卷', '物理合卷',
                                                  '化学', '总分']].mean().round(2)
            av_general = self.df[['语文A卷', '语文B卷', '语文合卷', '数学A卷', '数学B卷', '数学合卷',
                                  '英语A卷', '英语B卷', '英语合卷', '物理A卷', '物理B卷', '物理合卷',
                                  '化学', '总分']].apply(np.nanmean, axis=0).round(2)
            av_general.name = '年级平均分'
            av_scores = av_class.append(av_general)
            av_scores.reset_index(inplace=True)

            return av_scores
        elif '物理合卷' not in self.df.columns:
            av_class = self.df.groupby(['班级'])[['语文A卷', '语文B卷', '语文合卷', '数学A卷', '数学B卷', '数学合卷',
                                                  '英语A卷', '英语B卷', '英语合卷', '总分']].mean().round(2)
            av_general = self.df[['语文A卷', '语文B卷', '语文合卷', '数学A卷', '数学B卷', '数学合卷',
                                  '英语A卷', '英语B卷', '英语合卷', '总分']].apply(np.nanmean, axis=0).round(2)
            av_general.name = '年级平均分'
            av_scores = av_class.append(av_general)
            av_scores.reset_index(inplace=True)
            return av_scores
        else:
            av_class = self.df.groupby(['班级'])[['语文A卷', '语文B卷', '语文合卷', '数学A卷', '数学B卷', '数学合卷',
                                                  '英语A卷', '英语B卷', '英语合卷',
                                                  '物理A卷', '物理B卷', '物理合卷', '总分']].mean().round(2)
            av_general = self.df[['语文A卷', '语文B卷', '语文合卷', '数学A卷', '数学B卷', '数学合卷',
                                  '英语A卷', '英语B卷', '英语合卷', '物理A卷',
                                  '物理B卷', '物理合卷', '总分']].apply(np.nanmean, axis=0).round(2)
            av_general.name = '年级平均分'
            av_scores = av_class.append(av_general)
            av_scores.reset_index(inplace=True)
            return av_scores

    def get_goodscores(self, goodtotal):
        """
        计算各科有效分
        goodtotal:划线总分，高线，中线，低线
        """
        if '化学' in self.df.columns:
            good_score_df = self.df.loc[:,
                            ['序号', '姓名', '班级', '语文合卷', '数学合卷', '英语合卷', '物理合卷', '化学', '总分']]
            # good_score_df.columns = ['序号', '姓名', '班级', '语文', '数学', '英语', '物理', '化学', '总分']

            goodscoredata = good_score_df.loc[good_score_df['总分'] >= goodtotal]
            chnav = goodscoredata['语文合卷'].mean()
            mathav = goodscoredata['数学合卷'].mean()
            engav = goodscoredata['英语合卷'].mean()
            phyav = goodscoredata['物理合卷'].mean()
            chemav = goodscoredata['化学'].mean()
            totalav = goodscoredata['总分'].mean()
            factor = goodtotal / totalav
            chn = round(chnav * factor)
            math = round(mathav * factor)
            eng = round(engav * factor)
            phy = round(phyav * factor)
            chem = round(chemav * factor)
            if (chn + math + eng + phy + chem) > goodtotal:
                math -= 1
            if (chn + math + eng + phy + chem) < goodtotal:
                chn += 1
            return chn, math, eng, phy, chem, goodtotal

        elif '物理合卷' not in self.df.columns:
            good_score_df = self.df.loc[:, ['序号', '姓名', '班级', '语文合卷', '数学合卷', '英语合卷', '总分']]
            # good_score_df.columns = ['序号', '姓名', '班级', '语文', '数学', '英语', '总分']

            goodscoredata = good_score_df.loc[good_score_df['总分'] >= goodtotal]
            chnav = goodscoredata['语文合卷'].mean()
            mathav = goodscoredata['数学合卷'].mean()
            engav = goodscoredata['英语合卷'].mean()
            totalav = goodscoredata['总分'].mean()

            factor = goodtotal / totalav

            chn = round(chnav * factor)
            math = round(mathav * factor)
            eng = round(engav * factor)

            if (chn + math + eng) > goodtotal:
                math -= 1
            if (chn + math + eng) < goodtotal:
                chn += 1
            return chn, math, eng, goodtotal

        else:
            good_score_df = self.df.loc[:,
                            ['序号', '姓名', '班级', '语文合卷', '数学合卷', '英语合卷', '物理合卷', '总分']]
            # good_score_df.columns = ['序号', '姓名', '班级', '语文', '数学', '英语', '物理', '总分']
            goodscoredata = good_score_df.loc[good_score_df['总分'] >= goodtotal]
            chnav = goodscoredata['语文合卷'].mean()
            mathav = goodscoredata['数学合卷'].mean()
            engav = goodscoredata['英语合卷'].mean()
            phyav = goodscoredata['物理合卷'].mean()
            totalav = goodscoredata['总分'].mean()
            factor = goodtotal / totalav
            chn = round(chnav * factor)
            math = round(mathav * factor)
            eng = round(engav * factor)
            phy = round(phyav * factor)
            if (chn + math + eng + phy) > goodtotal:
                math -= 1
            if (chn + math + eng + phy) < goodtotal:
                chn += 1
            return chn, math, eng, phy, goodtotal

    def goodscore_process(self, goodtotal):
        """
        计算各科各班单有效和双有效人数
        """
        # 计算各班有效学生人数

        if '化学' in self.df.columns:
            good_score_df = self.df.loc[:,
                            ['序号', '姓名', '班级', '语文合卷', '数学合卷', '英语合卷', '物理合卷', '化学', '总分']]
            good_score_df.columns = ['序号', '姓名', '班级', '语文', '数学', '英语', '物理', '化学', '总分']

            chn, math, eng, phy, chem, total = self.get_goodscores(goodtotal)
            single_chn, double_chn = self.get_single_double_score(good_score_df, '语文', chn, total)
            single_math, double_math = self.get_single_double_score(good_score_df, '数学', math, total)
            single_eng, double_eng = self.get_single_double_score(good_score_df, '英语', eng, total)
            single_phy, double_phy = self.get_single_double_score(good_score_df, '物理', phy, total)
            single_chem, double_chem = self.get_single_double_score(good_score_df, '化学', chem, total)
            single_total, double_total = self.get_single_double_score(good_score_df, '总分', total, total)

            # 计算参考人数
            name_num = good_score_df.groupby(['班级'])['姓名'].count()
            name_num.name = '参考人数'

            goodscore_dict = {'参考人数': ' ', '语文': chn, '数学': math, '英语': eng,
                              '物理': phy, '化学': chem, '总分': total}
            goodscore_df = pd.DataFrame(goodscore_dict, index=['有效分数'])

            result_single = pd.concat([name_num, single_chn, single_math, single_eng,
                                       single_phy, single_chem, single_total],
                                      axis=1)

            result_double = pd.concat(
                [name_num, double_chn, double_math, double_eng,
                 double_phy, double_chem, double_total], axis=1)

            result_single.loc['共计'] = [result_single['参考人数'].sum(),
                                         result_single['语文'].sum(),
                                         result_single['数学'].sum(),
                                         result_single['英语'].sum(),
                                         result_single['物理'].sum(),
                                         result_single['化学'].sum(),
                                         result_single['总分'].sum()
                                         ]
            # 新增上线率一列并用百分数表示
            result_single['上线率'] = result_single['总分'] / result_single['参考人数']
            result_single['上线率'] = result_single['上线率'].apply(lambda x: format(x, '.2%'))
            # 新增一行文科共计。
            result_double.loc['共计'] = [result_double['参考人数'].sum(),
                                         result_double['语文'].sum(),
                                         result_double['数学'].sum(),
                                         result_double['英语'].sum(),
                                         result_double['物理'].sum(),
                                         result_double['化学'].sum(),
                                         result_double['总分'].sum()
                                         ]
            final_result = pd.concat([goodscore_df, result_single, result_double], keys=['有效分', '单有效', '双有效'])

            return final_result
        elif '物理合卷' not in self.df.columns:
            good_score_df = self.df.loc[:, ['序号', '姓名', '班级', '语文合卷', '数学合卷', '英语合卷', '总分']]
            good_score_df.columns = ['序号', '姓名', '班级', '语文', '数学', '英语', '总分']

            chn, math, eng, total = self.get_goodscores(goodtotal)
            single_chn, double_chn = self.get_single_double_score(good_score_df, '语文', chn, total)
            single_math, double_math = self.get_single_double_score(good_score_df, '数学', math, total)
            single_eng, double_eng = self.get_single_double_score(good_score_df, '英语', eng, total)
            single_total, double_total = self.get_single_double_score(good_score_df, '总分', total, total)

            # 计算参考人数
            name_num = good_score_df.groupby(['班级'])['姓名'].count()
            name_num.name = '参考人数'

            goodscore_dict = {'参考人数': ' ', '语文': chn, '数学': math, '英语': eng, '总分': total}
            goodscore_df = pd.DataFrame(goodscore_dict, index=['有效分数'])

            result_single = pd.concat([name_num, single_chn, single_math, single_eng, single_total], axis=1)

            result_double = pd.concat(
                [name_num, double_chn, double_math, double_eng, double_total], axis=1)

            result_single.loc['共计'] = [result_single['参考人数'].sum(),
                                         result_single['语文'].sum(),
                                         result_single['数学'].sum(),
                                         result_single['英语'].sum(),
                                         result_single['总分'].sum()]
            # 新增上线率一列并用百分数表示
            result_single['上线率'] = result_single['总分'] / result_single['参考人数']
            result_single['上线率'] = result_single['上线率'].apply(lambda x: format(x, '.2%'))
            # 新增一行文科共计。
            result_double.loc['共计'] = [result_double['参考人数'].sum(),
                                         result_double['语文'].sum(),
                                         result_double['数学'].sum(),
                                         result_double['英语'].sum(),
                                         result_double['总分'].sum()]
            final_result = pd.concat([goodscore_df, result_single, result_double], keys=['有效分', '单有效', '双有效'])

            return final_result

        else:
            good_score_df = self.df.loc[:,
                            ['序号', '姓名', '班级', '语文合卷', '数学合卷', '英语合卷', '物理合卷', '总分']]
            good_score_df.columns = ['序号', '姓名', '班级', '语文', '数学', '英语', '物理', '总分']
            chn, math, eng, phy, total = self.get_goodscores(goodtotal)
            single_chn, double_chn = self.get_single_double_score(good_score_df, '语文', chn, total)
            single_math, double_math = self.get_single_double_score(good_score_df, '数学', math, total)
            single_eng, double_eng = self.get_single_double_score(good_score_df, '英语', eng, total)
            single_phy, double_phy = self.get_single_double_score(good_score_df, '物理', phy, total)
            single_total, double_total = self.get_single_double_score(good_score_df, '总分', total, total)

            # 计算参考人数
            name_num = good_score_df.groupby(['班级'])['姓名'].count()
            name_num.name = '参考人数'

            goodscore_dict = {'参考人数': ' ', '语文': chn, '数学': math, '英语': eng,
                              '物理': phy, '总分': total}
            goodscore_df = pd.DataFrame(goodscore_dict, index=['有效分数'])

            result_single = pd.concat([name_num, single_chn, single_math, single_eng,
                                       single_phy, single_total],
                                      axis=1)

            result_double = pd.concat(
                [name_num, double_chn, double_math, double_eng,
                 double_phy, double_total], axis=1)

            result_single.loc['共计'] = [result_single['参考人数'].sum(),
                                         result_single['语文'].sum(),
                                         result_single['数学'].sum(),
                                         result_single['英语'].sum(),
                                         result_single['物理'].sum(),
                                         result_single['总分'].sum()
                                         ]
            # 新增上线率一列并用百分数表示
            result_single['上线率'] = result_single['总分'] / result_single['参考人数']
            result_single['上线率'] = result_single['上线率'].apply(lambda x: format(x, '.2%'))
            # 新增一行文科共计。
            result_double.loc['共计'] = [result_double['参考人数'].sum(),
                                         result_double['语文'].sum(),
                                         result_double['数学'].sum(),
                                         result_double['英语'].sum(),
                                         result_double['物理'].sum(),
                                         result_double['总分'].sum()
                                         ]
            final_result = pd.concat([goodscore_df, result_single, result_double], keys=['有效分', '单有效', '双有效'])

            return final_result

    def concat_results(self, goodtatal=None):
        good_score_results = self.goodscore_process(goodtatal)
        junior_av = self.get_av()
        junior_qualification = self.junior_scores()
        good_score_results = ScoreAnalysis.write_open(good_score_results)
        junior_av = ScoreAnalysis.write_open(junior_av)
        junior_qualification = ScoreAnalysis.write_open(junior_qualification)
        final_results = pd.concat([good_score_results, junior_av, junior_qualification])
        # with pd.ExcelWriter(r'D:\成绩统计结果\初中成绩统计分析.xlsx') as writer:
        #     final_results.to_excel(writer, sheet_name='成绩统计', index=False)
        final_file = ScoreAnalysis.file_name_by_time(final_results)
        final_file = os.path.basename(final_file)
        return final_file

    @staticmethod
    def get_single_double_score(data, subject, subject_score, total_score):
        single = data[data[subject] >= subject_score].groupby(['班级'])[subject].count()
        data_double = data[data['总分'] >= total_score]
        double = data_double[data_double[subject] >= subject_score].groupby(['班级'])[subject].count()
        return single, double


class ExamRoom(object):
    room_num = None

    def __init__(self, path):
        self.path = path

    def exam_room_info(self):
        """
        计算生成文理科各考室学生名单
        :return:
        """
        df_arts = pd.read_excel(self.path, sheet_name='文科',
                                dtype={'班级': str, '考号': str, '序号': str})
        df_science = pd.read_excel(self.path, sheet_name='理科',
                                   dtype={'班级': str, '考号': str, '序号': str})
        df_arts.sort_values(by='总分', ascending=False, inplace=True)
        df_science.sort_values(by='总分', ascending=False, inplace=True)
        room_numbers, room_numbers_science = self.get_room_number_arts_science(df_arts, df_science)

        # df_arts = df_arts.copy()
        # df_science = df_science.copy()

        df_arts['考室号'] = None
        df_arts['座位号'] = None
        df_arts = df_arts.loc[:, ['班级', '姓名', '考号', '考室号', '座位号']]
        df_arts.reset_index(drop=True, inplace=True)
        df_science['考室号'] = None
        df_science['座位号'] = None
        df_science = df_science.loc[:, ['班级', '姓名', '考号', '考室号', '座位号']]
        df_science.reset_index(drop=True, inplace=True)

        file_name = self.filename_by_time()
        writer = pd.ExcelWriter('D:\\成绩统计结果\\' + file_name)
        df_room_students = []
        df_room_students_science = []
        arts = pd.DataFrame(columns=df_arts.columns)
        science = pd.DataFrame(columns=df_science.columns)
        for idx, room_number in enumerate(room_numbers):
            begin = idx * ExamRoom.room_num
            end = begin + ExamRoom.room_num
            df_room_student = df_arts.iloc[begin:end]
            df_room_students.append((idx, room_number, df_room_student))

        for idx, room_number, df_room_student in df_room_students:
            for i in df_room_student.index:
                df_room_student = df_room_student.copy()
                df_room_student['考室号'].at[i] = room_number
                df_room_student['座位号'].at[i] = i + 1 if i < ExamRoom.room_num else i - idx * ExamRoom.room_num + 1
            df_room_student.to_excel(writer, sheet_name=room_number, index=False)
            arts = pd.concat([arts, df_room_student])

        for idx, room_number in enumerate(room_numbers_science):
            begin = idx * ExamRoom.room_num
            end = begin + ExamRoom.room_num
            df_room_student_science = df_science.iloc[begin:end]
            df_room_students_science.append((idx, room_number, df_room_student_science))
        for idx, room_number, df_room_student_science in df_room_students_science:
            for i in df_room_student_science.index:
                df_room_student_science = df_room_student_science.copy()
                df_room_student_science['考室号'].at[i] = room_number
                df_room_student_science['座位号'].at[
                    i] = i + 1 if i < ExamRoom.room_num else i - idx * ExamRoom.room_num + 1
            df_room_student_science.to_excel(writer, sheet_name=room_number, index=False)
            science = pd.concat([science, df_room_student_science])

        df_arts_general = arts.sort_values(by=['班级', '考室号'], ascending=[True, True])
        df_science_general = science.sort_values(by=['班级', '考室号'], ascending=[True, True])
        df_arts_general.to_excel(writer, sheet_name='文科', index=False)
        df_science_general.to_excel(writer, sheet_name='理科', index=False)
        # # 计算考生座签
        df_seat_arts = arts.copy()
        df_seat_science = science.copy()
        for i in df_seat_arts.index:
            df_seat_arts.loc[i + 0.5] = ['班级', '姓名', '考号', '考室号', '座位号']
        df_seat_arts.sort_index(inplace=True, ignore_index=True)
        for i in df_seat_science.index:
            df_seat_science.loc[i + 0.5] = ['班级', '姓名', '考号', '考室号', '座位号']
        df_seat_science.sort_index(inplace=True, ignore_index=True)
        df_seat_arts.to_excel(writer, sheet_name='文科座签', index=False)
        df_seat_science.to_excel(writer, sheet_name='理科座签', index=False)
        writer.close()
        # result_file = 'D:\\成绩统计结果\\' + file_name
        # final_file = os.path.basename(result_file)
        # final_file = self.file_zip(os.path.basename(result_file))
        return file_name

    @staticmethod
    def get_room_number_arts_science(df_arts, df_science):
        if len(df_arts) % ExamRoom.room_num != 0:
            room_numbers = [f'文科第{str(i + 1)}考室' for i in list(range(len(df_arts) // ExamRoom.room_num + 1))]
        else:
            room_numbers = [f'文科第{str(i + 1)}考室' for i in list(range(len(df_arts) // ExamRoom.room_num))]
        if len(df_science) % ExamRoom.room_num != 0:
            room_numbers_science = [f'理科第{str(i + 1)}考室' for i in
                                    list(range(len(df_science) // ExamRoom.room_num + 1))]
        else:
            room_numbers_science = [f'理科第{str(i + 1)}考室' for i in
                                    list(range(len(df_science) // ExamRoom.room_num))]
        return room_numbers, room_numbers_science

    @staticmethod
    def filename_by_time():
        file_time = time.time()
        file_name = f'{file_time}.xlsx'
        return file_name

    def exam_room(self):
        """
        计算生成高一文理不分科及初中各考室学生名单
        :return:
        """
        df = pd.read_excel(self.path, sheet_name='总表',
                           dtype={'班级': str, '考号': str, '序号': str})
        df.sort_values(by='总分', ascending=False, inplace=True)
        room_numbers = self.get_room_number(df)
        df['考室号'] = None
        df['座位号'] = None
        df = df.loc[:, ['班级', '姓名', '考号', '考室号', '座位号']]
        df.reset_index(drop=True, inplace=True)
        # print(room_numbers)
        df_room_students = []
        df_temp = pd.DataFrame(columns=df.columns)
        for idx, room_number in enumerate(room_numbers):
            begin = idx * ExamRoom.room_num
            end = begin + ExamRoom.room_num
            df_room_student = df.iloc[begin:end]
            df_room_students.append((idx, room_number, df_room_student))

        file_name = self.filename_by_time()
        writer = pd.ExcelWriter('D:\\成绩统计结果\\' + file_name)

        for idx, room_number, df_room_student in df_room_students:
            for i in df_room_student.index:
                df_room_student = df_room_student.copy()
                df_room_student['考室号'].at[i] = room_number
                df_room_student['座位号'].at[
                    i] = i + 1 if i < ExamRoom.room_num else i - idx * ExamRoom.room_num + 1
            df_room_student.to_excel(writer, sheet_name=room_number, index=False)
            df_temp = pd.concat([df_temp, df_room_student])
        df_temp.to_excel(writer, sheet_name='考室安排表', index=False)

        for i in df_temp.index:
            df_temp.loc[i + 0.5] = ['班级', '姓名', '考号', '考室号', '座位号']
        seat_label = df_temp.sort_index()
        seat_label.to_excel(writer, sheet_name='学生座签', index=False)
        writer.close()
        # result_file = 'D:\\成绩统计结果\\' + file_name
        # final_file = os.path.basename(result_file)
        # final_file = self.file_zip(os.path.basename(result_file))
        return file_name

    @staticmethod
    def get_room_number(df):
        if len(df) % ExamRoom.room_num != 0:
            room_numbers = [f'第{str(i + 1)}考室' for i in list(range(len(df) // ExamRoom.room_num + 1))]
        else:
            room_numbers = [f'第{str(i + 1)}考室' for i in list(range(len(df) // ExamRoom.room_num))]
        return room_numbers

    def exam_room_choice(self):
        try:
            result_file = self.exam_room_info()
            return result_file
        except:
            result_file = self.exam_room()
            return result_file


class ExamInvigilators(object):
    """
    获取监考安排表
    """
    exam_numbers = None
    room_numbers = None

    def __init__(self, filepath):
        self.filepath = filepath
        self.df = pd.read_excel(self.filepath, index_col='序号', dtype={'序号': str})

    def invigilation_table(self):

        df_teachers = self.df.copy()
        df_teachers = df_teachers.loc[:, '姓名']
        teacher_list = df_teachers.values
        random.seed(0)
        random.shuffle(teacher_list)
        df_room = pd.DataFrame(columns=['科目' + str(i + 1) for i in range(ExamInvigilators.exam_numbers)],
                               index=['第' + str(i + 1) + '考室' for i in range(ExamInvigilators.room_numbers)])
        df_room.index.rename('考室号', inplace=True)
        df_room.reset_index(inplace=True)
        nums = 0
        try:
            for i in df_room.columns[1:]:
                for k in df_room.index:
                    df_room[i].at[k] = teacher_list[nums]
                    if nums < len(teacher_list) - 1:
                        nums += 1
                    else:
                        nums = 0
        except KeyError:
            df_room.fillna(np.nan)

        df_room.set_index('考室号', inplace=True)
        return df_room

    def exam_teachers(self):
        """
        计算分考室监考教师名单
        """
        df_room = self.invigilation_table()
        file_name = self.get_file_name()
        writer = pd.ExcelWriter('D:\\成绩统计结果\\' + file_name)
        df_room.to_excel(writer, sheet_name='监考安排表')
        teachers = df_room.values
        teacher_dict = {}
        for i in range(len(teachers)):
            for k in teachers[i]:
                teacher_dict[k] = teacher_dict.get(k, 0) + 1
        teacher_occupied = list(teacher_dict.items())
        teacher_occupied.sort(key=lambda x: x[1], reverse=True)
        invigilators = []
        invigilation_times = []
        for i in range(len(teacher_occupied)):
            teacher, times = teacher_occupied[i]
            invigilators.append(teacher)
            invigilation_times.append(times)
        #     print('{0:>6}老师：    监考{1:<}节'.format(teacher, times))
        # print(f'共有{len(teacher_occupied)}个老师参与本次监考')
        invigilation_df = pd.DataFrame({'序号': [i + 1 for i in range(len(invigilators))], '监考老师': invigilators,
                                        '监考次数': invigilation_times})
        invigilation_df.set_index('序号', inplace=True)
        invigilation_df.loc['共计'] = [invigilation_df['监考老师'].count(),
                                       invigilation_df['监考次数'].sum()]
        invigilation_df.to_excel(writer, sheet_name='监考次数统计')
        writer.close()
        # result_file = 'D:\\成绩统计结果\\' + file_name
        # final_file = os.path.basename(result_file)

        return file_name

    @staticmethod
    def get_file_name():
        file_time = time.time()
        file_name = f'{file_time}.xlsx'
        return file_name


class SplitClass(object):
    def __init__(self, path):
        self.path = path
        self.df = pd.read_excel(self.path, sheet_name=0, dtype={
            '考号': str,
            '考生号': str
        })

    def split_into_class(self):
        class_numbers = self.df['班级'].unique()
        whole_df = pd.DataFrame()
        for i in class_numbers:
            class_number = self.df[self.df['班级'] == i].reset_index(drop=True)
            class_number['序号'] = [k + 1 for k in class_number.index]
            whole_df = pd.concat([whole_df, class_number])
        filename = ExamRoom.filename_by_time()
        writer = pd.ExcelWriter('D:\\成绩统计结果\\' + filename)
        whole_df.to_excel(writer, sheet_name='班级序号表', index=False)
        # final_file = os.path.basename(writer)
        writer.close()
        return filename


class CatalogueCourses(object):

    def __init__(self, file_path):
        self.file_path = file_path

    def file_open(self):
        df = pd.read_excel(self.file_path, sheet_name=0)
        return df

    def statistics_for_courses(self):
        data = self.file_open()
        class_count = self.get_course_data(data)
        # writer = pd.ExcelWriter(r'D:\成绩统计结果\汇总统计表.xlsx')
        # class_count.to_excel(writer, sheet_name='统计表', na_rep=None)
        # writer.close()
        return class_count

    def get_course_data(self, data):
        courses = data.columns[3:].tolist()
        class_count = data.groupby('班级')[courses].count()
        general_count = data[courses].count()
        class_count.loc['年级'] = general_count
        class_count['合计'] = class_count[courses].apply(np.sum, axis=1)
        class_count['班级人数'] = data['班级'].value_counts()
        class_count['班级人数'].at['年级'] = class_count['班级人数'].sum()
        class_count = class_count[['班级人数'] + courses + ['合计']]
        class_count.reset_index(inplace=True)
        class_count['班级人数'] = class_count['班级人数'].astype(int)
        return class_count

    def split_by_subject(self):
        data = self.file_open()
        class_count = self.get_course_data(data)
        courses = data.columns[3:].tolist()
        filename = ExamRoom.filename_by_time()
        writer = pd.ExcelWriter('D:\\成绩统计结果\\' + filename)
        class_count.to_excel(writer, sheet_name='汇总统计表', index=False)
        for i in range(len(courses)):
            new_data = data.loc[data[courses[i]].notnull(),
            ['序号', '姓名', '班级', courses[i]]]
            new_data.reset_index(inplace=True, drop=True)
            new_data['序号'] = [k + 1 for k in new_data.index]
            new_data.to_excel(writer, sheet_name=courses[i], index=False)
        writer.close()
        return filename


class GetInfoFromId(object):
    def __init__(self, filename):
        self.filename = filename

    def make_regions_dict(self, regionNo, region):
        """
        生成身份证号前6位地区字典
        :param regionNo:
        :param region:
        :return:
        """
        filename = r'./static/id_region_2024.xlsx'
        data = pd.read_excel(filename, sheet_name=0, dtype={'cityNo': str,
                                                            'districtNo': str,
                                                            'provinceNo': str})
        code_list = data[regionNo].tolist()
        district_list = data[region].tolist()
        region_dict = dict(zip(code_list, district_list))
        return region_dict

    def open_file(self):
        data = pd.read_excel(self.filename, dtype={'身份证号': str}, sheet_name=0)
        return data

    def data_remove_string(self, data, remove_string, data_column='身份证号'):
        """
        去除身份证号的多余字符,主要用于把学籍号转换成身份证号
        :param data:
        :param data_column: 在excel表上新建身份证号一列，把学籍号一列复制到这一列。
        :param remove_string: 要去除的字符。
        :return: 返回整理好的DataFrame数据
        """
        data[data_column] = data[data_column].str.replace(remove_string, '')
        return data

    # 清洗身份证号数据格式
    def get_clean_id(self):
        data = self.open_file()
        re_pattern = r'^([1-9]\d{5}[12]\d{3}(0[1-9]|1[012])(0[1-9]|[12][0-9]|3[01])\d{3}[0-9xX])$'

        # data = data.loc[data['身份证号'].notna()].reset_index()
        bad_df = pd.DataFrame(columns=data.columns)

        id_nums = []
        id_nums_bad = []
        for i in data.index:
            data['身份证号'] = data['身份证号'].astype(str)
            id_num = data['身份证号'].at[i].strip('\t').strip()
            if re.match(re_pattern, id_num):
                id_nums.append(id_num)
            else:
                id_nums_bad.append(id_num)
                bad_df.loc[i] = data.loc[i]
                data.drop(index=[i], inplace=True)
        data['身份证号'] = id_nums
        bad_df['身份证号'] = id_nums_bad
        data.reset_index(inplace=True, drop=True)
        if len(id_nums_bad) != 0:
            # print(f'错误的身份证号：{id_nums_bad},共计有{len(id_nums_bad)}个错误。请修正后再次运行。')
            bad_df = bad_df.loc[:, ['姓名', '身份证号']]
            # print(bad_df)
        # else:
        #     print("太棒了！经电脑检测，所有身份证号无误。信息提取完整！谢谢使用！")
        return data, bad_df, id_nums_bad

    def get_sex_birth_age(self):
        data, bad_df, id_bad = self.get_clean_id()
        regions = ['华北', '东北', '华南', '中南', '西南', '西北', '港澳台']
        province_dict = {
            '11': '北京市', '12': '天津市', '13': '河北省', '14': '山西省', '15': '内蒙古自治区',
            '21': '辽宁省', '22': '吉林省', '23': '黑龙江省',
            '31': '上海市', '32': '江苏省', '33': '浙江省', '34': '安徽省', '35': '福建省', '36': '江西省',
            '37': '山东省',
            '41': '河南省', '42': '湖北省', '43': '湖南省', '44': '广东省', '45': '广西壮族自治区', '46': '海南省',
            '51': '四川省', '52': '贵州省', '53': '云南省', '54': '西藏自治区', '50': '重庆市',
            '61': '陕西省', '62': '甘肃省', '63': '青海省', '64': '宁夏回族自治区', '65': '新疆维吾尔自治区',
            '83': '台湾地区', '81': '香港特别行政区', '82': '澳门特别行政区'
        }
        district_dict = self.make_regions_dict('districtNo', 'city_district')
        birth_date = []
        ages = []
        data['性别'] = ['女' if int(data['身份证号'].at[i][-2]) % 2 == 0 else '男' for i in data.index]
        region_num = [data['身份证号'].at[i][0] for i in data.index]
        province_num = [data['身份证号'].at[i][0:2] for i in data.index]
        district_num = [data['身份证号'].at[i][0:6] for i in data.index]
        df_year = [data['身份证号'].at[i][6:10] for i in data.index]
        df_month = [data['身份证号'].at[i][10:12] for i in data.index]
        df_day = [data['身份证号'].at[i][12:14] for i in data.index]
        data['所在区域'] = None
        for i in data.index:
            birth = datetime.date(int(df_year[i]), int(df_month[i]), int(df_day[i]))
            birth = birth.strftime('%Y年%m月%d日')
            age = datetime.datetime.today().year - int(df_year[i])
            ages.append(age)
            birth_date.append(birth)
        data['出生日期'] = birth_date
        data['年龄'] = ages
        region = [regions[int(num) - 1] for num in region_num]
        province = [province_dict.get(num, 'nodata') for num in province_num]
        district = [district_dict.get(num, 'nodata') for num in district_num]
        data['所在区域'] = region
        data['所属省份'] = province
        data['地区'] = district
        # print(data['地区'])
        if '序号' not in data.columns.tolist():
            data.insert(loc=0, column='序号', value=1)
        data['序号'] = [str(i + 1) for i in data.index]
        filename = ExamRoom.filename_by_time()
        data.to_excel('D:\\成绩统计结果\\' + filename, index=False)
        show_data = data.loc[:,
                    ['序号', '姓名', '身份证号', '性别', '出生日期', '年龄', '所属省份', '地区', '所在区域']]
        return filename, bad_df, id_bad, show_data


class GaokaoData2025:
    """
    从2025年起四川采用新高考（3+1+2)模式。这是专门针对新高考模式的政治, 地理, 生物, 化学四门赋分学科而设计的程序，
    以帮助高中年级老师做成绩分析用。还可以计算各班平均分及占比，有效分统计等。
    该程序设计用于帮助高中教师分析新高考模式下的学生成绩，特别是政治、地理、生物和化学四门学科的赋分情况。
    程序可以读取Excel文件中的成绩数据，计算各学科的赋分等级和赋分值，统计班级平均分，各科有效分并生成包含这些分析结果的Excel文件。
    注意：python 3.13,pandas 2.4 版本测试正常。在 python 3.9,pandas 1.4版本测试中，把 map换成applymap后,程序可正常运行。
    """
    # 各个等级赋分区间（T1——T2）
    # A_T_range = [86, 100]
    # B_T_range = [71, 85]
    # C_T_range = [56, 70]
    # D_T_range = [41, 55]
    # E_T_range = [30, 40]
    A_T_range = [76, 90]
    B_T_range = [61, 75]
    C_T_range = [46, 60]
    D_T_range = [31, 45]
    E_T_range = [20, 30]

    # line_ratio = None
    physics_line_ratio = None
    history_line_ratio = None

    def __init__(self, file):
        self.file = file
        # excel_file = pd.ExcelFile(self.file)
        # self.sheet_names = [sheet_name for sheet_name in excel_file.sheet_names if
        #                     sheet_name in ['物理类', '历史类', '总表']]
        # self.data_list = [pd.read_excel(self.file, sheet_name=sheet_name, dtype={'考号': str, '考生号': str}) for
        #                   sheet_name in
        #                   self.sheet_names]
        self.df = pd.read_excel(self.file, sheet_name='总表')

    def __str__(self):
        return os.path.basename(self.file)

    def get_grade_data(self):
        """
        分别获取政治，地理，化学和生物学科的赋分等级和赋分值后生成excel数据表
        :return: None
        """
        data = self.df
        # data_added = []
        # for item in range(len(data)):
        subjects = [col for col in data.columns if col in ['政治', '地理', '生物', '化学']]
        print(f'各学科等级人数及卷面分值区间'.center(100, '*'))
        subject_added_data = []
        for subject in subjects:
            data_item = self.get_grade(data, subject)
            subject_added_data.append(data_item)
        # subject_added_data列中，最后一个元素则是所有学科都已有赋分的data，所以下标取-1
        subjects_added_data = subject_added_data[-1]
        # subjects_added_data = pd.concat(subject_added_data, ignore_index=False,join='inner', axis=1)
        subjects_added_data_cols = [col for col in subjects_added_data.columns if
                                    col in ['语文', '数学', '英语', '物理', '历史', '政治赋分', '地理赋分',
                                            '生物赋分', '化学赋分']]
        # 新版pandass要用astype(float).round(2)转换成2位小数。
        subjects_added_data['总分赋分'] = subjects_added_data[subjects_added_data_cols].sum(axis=1).astype(
            float).round(2)
        subjects_added_data.sort_values(by='总分赋分', ascending=False, inplace=True)
        subjects_added_data['序号'] = [i + 1 for i in range(len(subjects_added_data))]
        # data_added.append(subjects_added_data)
        return subjects_added_data

    def get_grade(self, data, subject):
        """
        分别获取政治，地理，化学和生物学科的赋分等级和赋分值
        :param data: df数据表
        :param subject: 学科
        :return: 返回获取了赋分等级和赋分值的df数据
        """
        max_score, min_score = self.get_subject_max_min_score(data, subject)
        data[subject + '等级'] = data[subject].apply(lambda x: self.get_level(x, min_score))
        data[subject + '赋分'] = data[subject].apply(
            lambda x: self.get_final_scores(x, min_score, max_score))
        return data

    @staticmethod
    def separate_data(data_mixed):
        """
         用于分科后考试，赋分后再生成物理类和历史类两个df，用于生成excel文件
        :param data_mixed:
        :return:物理类和历史类两个df
        """

        history_min = data_mixed['历史'].min(skipna=True)
        physics_min = data_mixed['物理'].min(skipna=True)
        data_history = data_mixed[data_mixed['历史'] >= history_min]
        # 删除多列
        data_history = data_history.copy()
        data_history.drop(['物理', '化学', '化学等级', '化学赋分'], axis=1, inplace=True)

        data_physics = data_mixed[data_mixed['物理'] >= physics_min]
        # 删除单列
        data_physics = data_physics.copy()
        del data_physics['历史']
        return data_physics, data_history

    def subjects_average(self, data, subjects):
        """
         计算各学科的平均分。
        :param data: df数据
        :param subjects: 参加计算平均分的学科
        :return: 返回一个df。包含有各班各科平均分，年级平均分和各科平均分在年级平均分中的占比，用于制作折线图
        """
        class_av = data.groupby('班级')[subjects].mean().round(2)
        # class_av.fillna(0, inplace=True)
        # 求这个平均数，只能用apply(np.mean)才行。用其它的如mean(),apply(np.nanmean)都会报错
        av_general = data[subjects].apply(np.mean, axis=0).round(2)
        # av_general = data[subjects].mean().round(2)
        # av_general.fillna(0, inplace=True)
        av_percentage = class_av / av_general.round(2)
        # av_percentage.fillna(0, inplace=True)
        # print(av_percentage)

        # pandas 2.0以上用map替换applymap
        av_percentage = av_percentage.applymap(lambda x: format(x, '.2%'))  # 以百分号显示
        av_percentage = av_percentage.applymap(lambda x: x.replace('nan%', ''))  # 不显示nan%
        av_percentage.to_excel('av_percentage.xlsx')

        av_percentage_cols = [col + '占比' for col in av_percentage.columns]
        col_dict = dict(zip(av_percentage.columns, av_percentage_cols))
        av_percentage.rename(columns=col_dict, inplace=True)

        class_av.loc['年级平均'] = av_general
        class_av['参考人数'] = data['班级'].value_counts()
        class_av.loc['年级平均', '参考人数'] = class_av['参考人数'].sum()
        final_av_percentage = pd.concat([class_av, av_percentage], axis=1)
        final_av_percentage = self.change_columns_order(final_av_percentage)
        return final_av_percentage

    def get_average_school(self, data):
        """
         计算赋分后作有学科的平均分及平均分占比
        :param data: df
        :return: 返回一个有df元素的列表。
        """
        # final_av = []
        subjects_av = [col for col in self.get_subjects(data) if col not in ['化学', '政治', '地理', '生物', '总分']]
        # print(subjects_av)

        final_av_percentage = self.subjects_average(data, subjects_av)
        # final_av.append(final_av_percentage)
        return final_av_percentage

    @staticmethod
    def get_subjects(data):
        subjects = [col for col in data.columns if
                    col in ['语文', '数学', '英语', '物理', '历史',
                            '化学', '政治', '地理', '生物',
                            '化学赋分', '政治赋分', '地理赋分', '生物赋分', '总分', '总分赋分']]
        return subjects

    def excel_school_files(self):
        """
         学校组织的考试，要对化学政治地理和生物四科赋分。excel文件上要有一个总表工作表（sheet)
        """

        # file_name = os.path.basename(
        #     self.file).split('.')[
        #                 0] + f'--物理类{physics_line_percentage}+历史类{history_line_percentage}划线成绩分析统计结果.xlsx'
        file_name = self.filename_by_time()

        scores_added_data = self.get_grade_data()
        writer = pd.ExcelWriter('D:\\成绩统计结果\\'  + file_name)
        # for item in range(len(scores_added_data_list)):
        scores_added_data.to_excel(writer, sheet_name='赋分表',
                                   index=False,
                                   float_format='%.2f')
        # print('================================================')
        # print(scores_added_data)
        physics_data, history_data = self.separate_data(scores_added_data)
        physics_data.reset_index(drop=True, inplace=True)
        history_data.reset_index(drop=True, inplace=True)
        physics_data['排名'] = physics_data['总分赋分'].rank(method='min', ascending=False)
        physics_data['序号'] = [i + 1 for i in physics_data.index]
        history_data['排名'] = history_data['总分赋分'].rank(method='min', ascending=False)
        history_data['序号'] = [i + 1 for i in history_data.index]
        #
        average_data_physics = self.get_average_school(physics_data)
        average_data_history = self.get_average_school(history_data)
        # av_history, av_physics, good_history, good_physics = self.get_final_processed_data()
        average_data_physics.to_excel(writer, sheet_name='物理类平均分', float_format='%.2f')
        average_data_history.to_excel(writer, sheet_name='历史类平均分', float_format='%.2f')
        # print(average_data_history)

        good_scores_data_physics, unmatched_physics, assessment_physics = self.good_scores_school(
            physics_data, GaokaoData2025.physics_line_ratio)
        good_scores_data_history, unmatched_history, assessment_history = self.good_scores_school(
            history_data, GaokaoData2025.history_line_ratio)
        good_scores_data_history.to_excel(writer, sheet_name='历史类有效分')
        good_scores_data_physics.to_excel(writer, sheet_name='物理类有效分')

        physics_data.to_excel(writer, sheet_name='-物理类', index=False)
        history_data.to_excel(writer, sheet_name='-历史类', index=False)

        unmatched_physics.to_excel(writer, sheet_name='物理类错位生名单', index=False)
        unmatched_history.to_excel(writer, sheet_name='历史类错位生名单', index=False)

        assessment_physics.to_excel(writer, sheet_name='物理类学科评价')
        assessment_history.to_excel(writer, sheet_name='历史类学科评价')

        writer.close()
        return file_name, average_data_history, average_data_physics, good_scores_data_history, good_scores_data_physics

    def get_final_scores(self, score, min_score, max_score):
        """
        计算获取学生卷面分数的赋分值
        :param score:
        :param min_score:
        :param max_score:
        :return:
        """
        a_t = GaokaoData2025.A_T_range
        b_t = GaokaoData2025.B_T_range
        c_t = GaokaoData2025.C_T_range
        d_t = GaokaoData2025.D_T_range
        e_t = GaokaoData2025.E_T_range
        if score >= min_score[0]:
            score_added = self.get_added_score(score, min_score[0], max_score[0], a_t[0], a_t[1])
            return score_added
        elif score >= min_score[1]:
            score_added = self.get_added_score(score, min_score[1], max_score[1], b_t[0], b_t[1])
            return score_added
        elif score >= min_score[2]:
            score_added = self.get_added_score(score, min_score[2], max_score[2], c_t[0], c_t[1])
            return score_added
        elif score >= min_score[3]:
            score_added = self.get_added_score(score, min_score[3], max_score[3], d_t[0], d_t[1])
            return score_added
        elif score < min_score[3]:
            score_added = self.get_added_score(score, min_score[4], max_score[4], e_t[0], e_t[1])
            return score_added

    def good_scores_school(self, data, line_ratio):
        """
        用于学校考试的有效分统计，有效分数是由指定的划线分数计算得到
        :param data: df
        :param line_ratio:
        :return: 返回一个有效分统计的df
        """
        good_scores_data, unmatched_student_names, assessment_data = self.get_single_double_school_data(data,
                                                                                                        line_ratio)
        return good_scores_data, unmatched_student_names, assessment_data

    def get_single_double_school_data(self, data, line_ratio):
        """
         按照新高考的模式，计算中线和高线的有效分，完成单有效和双有效的统计
        :param data:
        :param line_ratio:
        :return: 返回含有有效分数，单有效和双有效的df

        """
        good_total_line = self.get_good_total_line(data, '总分赋分', line_ratio)
        subjects = [col for col in self.get_subjects(data) if col not in ['化学', '生物', '政治', '地理', '总分']]
        double_subjects_num_list = []
        single_data_list = []
        double_data_list = []
        subjects_scores = []
        unmatched_students_list = []
        unmatched_subjects_num_list = []
        for subject in subjects:
            double_subject_num_series = self.get_double_subject_num_series(data, subject, '总分赋分', good_total_line)
            subject_good_score = self.get_subject_good_score(data, subject, line_ratio)
            subjects_scores.append(subject_good_score)

            single_data = self.get_single_subject_data(data, subject, subject_good_score)
            double_data = self.get_double_subject_data(data, subject, '总分赋分', subject_good_score,
                                                       good_total_line)
            subject_unmatched_student = self.get_unmatched_subject_students(data, '总分赋分', subject,
                                                                            subject_good_score, good_total_line)
            unmatched_subject_num = subject_unmatched_student.groupby('班级')[subject].count()
            double_subjects_num_list.append(double_subject_num_series)
            unmatched_subjects_num_list.append(unmatched_subject_num)
            unmatched_students_list.append(subject_unmatched_student)
            single_data_list.append(single_data)
            double_data_list.append(double_data)
        # 计算选科后各班各科总分上线人数
        double_subjects_num_df = pd.concat(double_subjects_num_list, axis=1)
        self.add_total_num_row(double_subjects_num_df)

        # print(double_subjects_num_df)
        # 获取有效分数并转换成df
        good_scores_dict = dict(zip(subjects, subjects_scores))
        good_scores_df = pd.DataFrame(good_scores_dict, index=[0])
        # 合成单有效和双有效统计的df
        single_data = pd.concat(single_data_list, axis=1)
        double_data = pd.concat(double_data_list, axis=1)
        single_data.sort_index(inplace=True)
        double_data.sort_index(inplace=True)
        # 生成各科错位学生名单
        unmatched_students_by_class = pd.concat(unmatched_students_list, axis=1)
        unmatched_students_by_class.dropna(inplace=True, how='all', axis=1)
        # 计算错位人数，用于选科后统计错位生人数
        unmatched_subjects_num = pd.concat(unmatched_subjects_num_list, axis=1)
        self.add_total_num_row(unmatched_subjects_num)
        # unmatched_subjects_num.replace(0, np.nan, inplace=True)
        # 单有效统计：增加一列参考人数和一行年级共计
        single_data['参考人数'] = data['班级'].value_counts()
        self.add_total_num_row(single_data)

        # 增加一列上线率并用百分号显示
        single_data['上线率'] = single_data['总分赋分'] / single_data['参考人数']
        single_data['上线率'] = single_data['上线率'].apply(lambda x: format(x, '.2%'))  # 以百分号显示

        # 双有效统计：增加一列参考人数和一行年级共计
        double_data['参考人数'] = data['班级'].value_counts()
        self.add_total_num_row(double_data)
        # 计算学科评价，获取命中率，贡献率和评价等级
        assessment_df, shoot_df, contribution_df = self.get_assess_grade_df(single_data, double_data,
                                                                            double_subjects_num_df)
        subjects_assessment = [col for col in data.columns if
                               col in ['语文', '数学', '英语', '物理', '历史', '化学赋分', '生物赋分', '政治赋分',
                                       '地理赋分']]
        # 给各班各学科评价赋值：A，B，C，D，E，F（或无数据）
        assessment_list = []
        for subject in subjects_assessment:
            subject_processing = self.grade_assessment(subject, shoot_df, contribution_df, single_data, assessment_df,
                                                       double_subjects_num_df)
            assessment_list.append(subject_processing)

        # print(assessment_df)
        # 注意：新版用map，旧版用applymap
        shoot_df = shoot_df.applymap(lambda x: format(x, '.2%'))
        contribution_df = contribution_df.applymap(lambda x: format(x, '.2%'))
        shoot_contribution_assessment = pd.concat([shoot_df, contribution_df, assessment_list[-1]], axis=0,
                                                  keys=['命中率', '贡献率', '学科评价'])

        combined_data = pd.concat(
            [good_scores_df, single_data, double_data, unmatched_subjects_num, assessment_list[-1]],
            keys=['有效分数', '单有效', '双有效', '错位人数', '学科评价'],
            axis=0)
        # print(assessment_list[-1])
        # 改变列顺序，把参考人数一列放在班级后面
        combined_data = self.change_columns_order(combined_data)
        # print(combined_data)

        return combined_data, unmatched_students_by_class, shoot_contribution_assessment

    @staticmethod
    def add_total_num_row(data):
        data.loc['年级共计'] = [data[col].sum() for col in data.columns]
        return

    @staticmethod
    def get_double_subject_num_series(data, subject, total_col, total):
        """
        新高考中，各班选科人数不同，需要计算出每一个学科的总分上线的人数。
        """
        # 用reindex(class_names, fill_value=0)确保所有班级数据不会消失，没有值时用0代替。
        class_names = data['班级'].drop_duplicates().tolist()
        # 选出各班各个学科的总人数
        subject_data = data[data[subject] >= data[subject].min()]
        # 选出各班各个学科总分上线的人数的series
        double_subject_num_series = subject_data[subject_data[total_col] >= total].groupby('班级')[
            subject].count().reindex(class_names, fill_value=0)

        return double_subject_num_series

    @staticmethod
    def grade_assessment(subject, shoot_df, contribution_df, single_df, grade_df, double_subjects_df):
        """"
        学科等级评价。各班选科各不相同，如某班的一个学科单有效人数为零，则视为这个班没有人选学该学科，等级为无数据或用F表示。
        """

        for i in shoot_df.index:
            if single_df[subject].at[i] != 0:
                if (single_df[subject].at[i]) >= double_subjects_df[subject].at[i] * 0.8:
                    if (contribution_df[subject].at[i] >= 0.7) & (shoot_df[subject].at[i] >= 0.6):
                        grade_df[subject].at[i] = 'A'
                    elif (contribution_df[subject].at[i] >= 0.7) & (shoot_df[subject].at[i] < 0.6):
                        grade_df[subject].at[i] = 'B'
                    elif (contribution_df[subject].at[i] < 0.7) & (shoot_df[subject].at[i] >= 0.6):
                        grade_df[subject].at[i] = 'C'
                    else:
                        grade_df[subject].at[i] = 'D'
                else:
                    grade_df[subject].at[i] = 'E'
            else:
                grade_df[subject].at[i] = '无数据'
        return grade_df

    @staticmethod
    def get_assess_grade_df(single, double, subjects_num_df):
        subjects = [subject for subject in single.columns if
                    subject in ['语文', '数学', '英语', '物理', '历史', '化学', '政治', '地理', '生物', '化学赋分',
                                '政治赋分', '地理赋分', '生物赋分']]
        single.fillna(0, inplace=True)
        double.fillna(0, inplace=True)
        subjects_num_df.fillna(0, inplace=True)
        shoot_dict_list = []
        contribution_dict_list = []
        for subject in subjects:
            shoot_subject = double[subject] / single[subject]
            shoot_dict_list.append(shoot_subject)
            # 计算贡献率，要用各班各学科的总分上线人数与该科的双有效人数相除。不能用各班的总分上线人线去除
            # subjects_num_df为所有学科总分上线人数的df(选学科目化学，政治，地理和生物的人数各班是不同的）
            contribution_subject = double[subject] / subjects_num_df[subject]
            contribution_dict_list.append(contribution_subject)
        shoot_dict = dict(zip(subjects, shoot_dict_list))
        shoot_df = pd.DataFrame(shoot_dict)
        contribution_dict = dict(zip(subjects, contribution_dict_list))
        contribution_df = pd.DataFrame(contribution_dict)
        shoot_df.fillna(0, inplace=True)
        contribution_df.fillna(0, inplace=True)
        grade_assess_df = pd.DataFrame(columns=contribution_df.columns, index=contribution_df.index)

        return grade_assess_df, shoot_df, contribution_df

    @staticmethod
    def get_unmatched_subject_students(data, total_col, subject, subject_score, total):
        """
        get the unmatched students in an exam
        :param data:
        :param subject:
        :param subject_score:
        :param total_col:
        :param total:
        :return:
        """
        unmatched_data = data[data[total_col] >= total]
        subject_unmatched_df = unmatched_data.loc[:, ['班级', '姓名', subject]].loc[
            unmatched_data[subject] < subject_score].sort_values(
            by=['班级', subject], ascending=[True, False]).reset_index(drop=True)
        return subject_unmatched_df

    @staticmethod
    def get_subject_max_min_score(data, subject):
        """
        用于计算获取各等级的卷面分区间（Y1-Y2)
        :param data:
        :param subject:
        :return: 各等级的卷面分区间（Y1-Y2)
        """
        max_score = []
        min_score = []
        subject_num = data[subject].count()
        A_num = int(subject_num * 0.15)
        B_num = int(subject_num * 0.35)
        C_num = int(subject_num * 0.35)
        D_num = int(subject_num * 0.13)
        data.sort_values(by=subject, ascending=False, inplace=True, ignore_index=True)
        # 计算A等级的卷面上下限分值及学生人数,由于每个等级的最低分会有不止一个，所以要再次切片
        data_subject_A = data.loc[:A_num - 1, subject]
        A_max = data_subject_A.max()
        A_min = data_subject_A.min()
        max_score.append(A_max)
        min_score.append(A_min)
        # 再次切片，确保A等级所有最低分都在同一个等级
        final_data_subject_A = data[data[subject] >= A_min]
        # 计算B等级的卷面上下限分值及学生人数
        data_subject_B = data.loc[len(final_data_subject_A):(len(final_data_subject_A) + B_num - 1), subject]
        B_max = data_subject_B.max()
        B_min = data_subject_B.min()
        max_score.append(B_max)
        min_score.append(B_min)
        # 再次切片，确保B等级所有最低分都在同一个等级
        final_data_subject_B = data[(data[subject] >= B_min) & (data[subject] <= B_max)]
        # 计算C等级的卷面上下限分值及学生人数
        data_subject_C = data.loc[(len(final_data_subject_A) + len(final_data_subject_B)):(
                len(final_data_subject_A) + len(final_data_subject_B) + C_num - 1), subject]
        C_max = data_subject_C.max()
        C_min = data_subject_C.min()
        max_score.append(C_max)
        min_score.append(C_min)
        # 再次切片，确保C等级所有最低分都在同一个等级
        final_data_subject_C = data[(data[subject] >= C_min) & (data[subject] <= C_max)]
        # 计算D等级的卷面上下限分值及学生人数
        data_subject_D = data.loc[(len(final_data_subject_A) + len(final_data_subject_B) + len(final_data_subject_C)):(
                len(final_data_subject_A) + len(final_data_subject_B) + len(final_data_subject_C) + D_num - 1), subject]
        d_max = data_subject_D.max()
        d_min = data_subject_D.min()
        max_score.append(d_max)
        min_score.append(d_min)
        # 再次切片，确保D等级所有最低分都在同一个等级
        final_data_subject_D = data[(data[subject] >= d_min) & (data[subject] <= d_max)]
        # 计算E等级的卷面上下限分值及学生人数
        final_data_subject_E = data.loc[
                               (len(final_data_subject_A) + len(final_data_subject_B) + len(final_data_subject_C) + len(
                                   final_data_subject_D)):subject_num - 1, subject]
        E_max = final_data_subject_E.max()
        E_min = final_data_subject_E.min()
        max_score.append(E_max)
        min_score.append(E_min)
        print(
            f'{subject}学科A等级人数为：{len(final_data_subject_A)},'
            f'B等级人数为：{len(final_data_subject_B)},'
            f'C等级人数为：{len(final_data_subject_C)},'
            f'D等级人数为：{len(final_data_subject_D)},'
            f'E等级人数为：{len(final_data_subject_E)}')
        # 新版pandas2.0以上，接收到的数字在列表中会显示成np.float64(81.0)，所以要用下标读取出来才能正常显示小数
        print(
            f'{subject}学科等级卷面Y1的值分别为：{min_score[0]}, {min_score[1]}, {min_score[2]}, {min_score[3]}, {min_score[4]}, '
            f'Y2的值分别为：{max_score[0]}, {max_score[1]}, {max_score[2]}, {max_score[3]}, {max_score[4]}')
        return max_score, min_score

    @staticmethod
    def get_single_subject_data(data, subject, subject_score):
        """
         计算获取一个学科单有效的df
        :param data: df
        :param subject: 学科
        :param subject_score: 一个学科的有效分数
        :return: 返回一个单有效的series
        """
        class_names = data['班级'].drop_duplicates().tolist()
        single_subject = data[data[subject] >= subject_score].groupby(['班级'])[
            subject].count().reindex(class_names, fill_value=0)
        return single_subject

    @staticmethod
    def get_double_subject_data(data, subject, total_col, subject_score, total):
        """
         计算获取学科双有校的df
        :param data: df
        :param subject: 学科
        :param total_col: 总分字段名
        :param subject_score: 学科有效分数
        :param total: 总分
        :return: 返回一个学科双有效的series
        """
        class_names = data['班级'].drop_duplicates().tolist()
        double_subject_data = data[data[total_col] >= total]
        double_subject = double_subject_data[double_subject_data[subject] >= subject_score].groupby('班级')[
            subject].count().reindex(class_names, fill_value=0)
        return double_subject

    @staticmethod
    def get_added_score(y, y1, y2, t1, t2):
        """
        计算赋分值的公式：高考赋分方法，其中y为原始卷面得分，t为赋值得分，
        t1和t2为所在等级赋值区间的下限和上限，y1和y2为卷面所在等级分数区间的下限和上限。
        :param y:
        :param y1:
        :param y2:
        :param t1:
        :param t2:
        :return: 一个学生的赋值得分（四舍五入取整）
        """
        t = symbols('t')
        try:
            if y == y1:
                scores_added = t1
                return scores_added
            else:
                scores_added = solve((t2 - t) / (t - t1) - (y2 - y) / (y - y1), t)
                return round(scores_added[0])
        except Exception as e:
            raise f'赋值时发生错误{e}'

    @staticmethod
    def get_level(score, min_score):
        """
         计算赋分学科的等级
        :param score: 学科的卷面得分
        :param min_score: 所在等级的最低分
        :return: 相应的等级
        """
        if score >= min_score[0]:
            return 'A'
        elif score >= min_score[1]:
            return 'B'
        elif score >= min_score[2]:
            return 'C'
        elif score >= min_score[3]:
            return 'D'
        elif score < min_score[3]:
            return 'E'

    @staticmethod
    def change_columns_order(data):
        col_first = '参考人数'
        cols_left = [col for col in data.columns if col != col_first]
        new_columns_data = data[[col_first] + cols_left]
        return new_columns_data

    @staticmethod
    def get_subject_good_score(data, subject, line_ratio):
        """
         计算学科有效分。按照总分上线率，计算学科有效分
        :param data: DataFrame
        :param subject: 学科
        :param line_ratio: 总分（分为高线和中线）
        :return: 学科的有效分（高线和低线）
        """
        # total_num = data.shape[0]
        # good_total_num = data.loc[data[total_col] >= GaokaoData2025.total_line].shape[0]
        # good_percent_ratio = good_total_num / total_num
        good_subject_num = int(data[subject].count() * line_ratio)
        data.sort_values(by=subject, ascending=False, inplace=True, ignore_index=True)
        good_subject_score = data.loc[:good_subject_num - 1, subject].min()

        return good_subject_score

    @staticmethod
    def get_good_total_line(data, total_col, line_ratio):
        """
        搂指定的比例计算划线线分数
        :param data:
        :param total_col:
        :param line_ratio
        :return:
        """
        data.sort_values(by=total_col, ascending=False, inplace=True, ignore_index=True)
        # total_num = data.shape[0]
        good_total_num = int(data[total_col].count() * line_ratio)
        good_total_line = data.loc[:good_total_num - 1, total_col].min()
        return good_total_line

    @staticmethod
    def filename_by_time():
        physics_line_percentage = format(GaokaoData2025.physics_line_ratio, '.0%')
        history_line_percentage = format(GaokaoData2025.history_line_ratio, '.0%')
        file_time = time.time()
        file_name = f'物理类{physics_line_percentage}+历史类{history_line_percentage}-{file_time}.xlsx'
        return file_name


